Dynamic YTD calculation

[h0raz0n]

Hi everyone

I have a set of data which is the total budget for the year and the monthly breakdown (ie. Budget full year, Budget Jan month-to-date, budget feb month-to-date,....., budget dec month-to-date). They are pulled from the server so I am unable to change how the data is presented.

The monthly values are calculated over a curve so I cannot just take the total and divide by 12.

I am trying to create a budget year-to-date column where it will sum up the monthly columns depending on the last completed month (ie. Month(Now())-1). So if last month was July, then I sum up the columns from Budget Jan year-to-date to Budget July year-to-date.

May I know how can I do that?

In excel I would simply use a Sum combined with Offset function.

Thanks.

 

[arnelgp]

are those columns on your table: "budget jan month-to-date', "budget feb month-to-date", etc.
it will be hard to calculate that way.
my approach would be to have a table with this structure.
in this way you can refine your summation (confining only to this year)

TableName: MTDBudget
--------------------------------------------------------------
FieldName	FieldType	Description
--------------------------------------------------------------
BudgetDate	Date/Time	Format Short Date (put the the first day of the month, ie: 01-jan-2017)
MTDBudget	Double		The Month to date budget for that month.

now for the YTD column that you want, you can use Select Query or use DSum:

Select Sum(MTDBudget) From MTDBudget Where BudgetDate < Date And Year(BudgetDate)=Year(Date);

or using DSum

DSum("MTDBudget","MTDBudget", "BudgetDate < Date And Year(BudgetDate)=Year(Date)")

 

[plog]

They are pulled from the server so I am unable to change how the data is presented.

That is incorrect. You absolutely can convert the data to a proper structure then work with it. Do that, then summing up year to date data is a simple aggregate query