Estimate Calculation (actuals + remaining budget)

Alex · May 6, 2019, 11:19am

Hi there,

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.

factFinancials
Date
Amount
Type (BUD/ACT)

dimDate
Date
Month
FY

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.

Nick_M · May 6, 2019, 10:18pm

@Alex -
Any chance you can load some sample data? Actuals vs. Budgets can most certainly be done, but would like to work some of your data if possible.

-Nick

sam.mckay · May 6, 2019, 10:51pm

The likely issue here I believe is the cumulative total pattern being used.

I think you want to be using something more like exampled here

You see here that SUMX is used

The issue is you’ve place essentially a cumulative total within the cumulative total with the DATESYTD. This always runs into trouble I’ve found.

That’s why this SUMX approach is a better one.

I detail quite a lot in the above video.

Also agree with Nick, a demo model would be helpful also if this doesn’t solve the issue for you.

Thanks

Alex · May 7, 2019, 11:16am

Thank you for a quick reponse, guys.

I have illustrated what’s going on the the attached .pbix file. As you see, the year-end estimate accumulates to 27 when it should end at 21.

Also, I’d like actuals and budget to filter according to the month-slicer, while estimate remains a full year measure, i.e. some ALLEXCEPT( dDate ; dDate[Year] )-logic. In other words, the x-axis of the chart shouldn’t respond to month filtering. Don’t know if this is possible.

Test.pbix (69.1 KB)

TestData.xlsx (10.9 KB)

Sam, I have actually already viewed your video and tried to implement some SUMX-logic but without any luck. I’ll give it another attempt.

Thank you.

Alex · May 7, 2019, 11:39am

It works!

I added two new meaures

EST MD = IF( ISBLANK( [ACT] ) ; [BUD] ; [ACT] )

To prioritize between actuals and budget on a monthly basis and

EST Acum = 
CALCULATE(
    SUMX( SUMMARIZE( dDate ; dDate[Date] ; "Estimate" ; [EST MD] ) ; [Estimate] );
        FILTER( ALLSELECTED( dDate );
            dDate[Date] <= MAX( dDate[Date] ) ) )

See screen shot below. I’d still like it not to respond to month filtering but I’ll play around with some ALL(EXCEPT)-logic.

Thank you both!