I’d like to calculate an Estimate-measure, where remaining budget is added to actuals YTD for a selected financial year. This should provide a year-end estimate of a certain cost category (budgets are allocated to cost groups and so are actuals so no granularity issue). The relevant tables in my datamodel are factFinancials and dimDate.
The end game is to provide an area chart consisting of three cumulative series; actuals YTD, budget YTD and estimate Full Year. I’d like to be able to slice on month so that actuals and budget YTD filters accordingly. Note that both actuals and budget are booked on 01-xx-201x each month - thus the logic around FirstDayOfLastMonth
Currently I’m attempting something like this:
Estimate = VAR FirstDayOfLastMonth = EOMONTH( FIRSTDATE( dimDate[Date] ) ; 1 ) + 1 VAR YTDACT = CALCULATE( [Realiseret VS] ; DATESYTD( dimDate[Date] ) ) VAR YTDBUD = CALCULATE( [Budget VS] ; DATESYTD( dimDate[Date] ) ) VAR YTDBUDCur = IF( ISBLANK( [Actuals MD] ) ; BLANK() ; CALCULATE( YTDBud ; FILTER( ALLSELECTED( dimDate[Date] ) ; dimDate[Date] <= FirstDayOfLastMonth ) ) ) RETURN YTDACT + (YTDBUD - YTDBUDCur)
But all this does is to add cumulative YTD BUD to cumulative YTD ACT in the cutting month. I have also tried to calculate on a monthly basis where actuals eventually are substituted by budget values and then use that measure in a cumulative calculation but this does not work either.
I have tried to illustrate my goal in the snip below.
Hope you guys are able to help.
Thanks in advance.