Calculated Run Rate Budget vs Actual diff.

[Jonas_Holm]

Hello everyone,

I have this really annoying problem, that I can't solve. I have tried to use this method in this yourtube, but comes up empty, since I can't come around the date. What's the missing LINK?! Combining ACTUALS and FORECAST as one LINE in Power BI - YouTube

The relationship is this: 

The data I have is this:

The measures, I have used is these:

1. 

Budget > today =

(CALCULATE([Revenue] - [Budget revenue], FILTER('Date','Date'[Date]<=TODAY())))

2.

Avg. daily run rate 2 =

VAR DaysWithSales = CALCULATE( COUNTROWS('Date'), FILTER(ALLSELECTED('Date'), [Revenue] > 0 ) )

VAR CumulativeTotal = CALCULATE([Budget > today], ALLSELECTED('Date'), REMOVEFILTERS('Date'[Date]))

RETURN

DIVIDE(CumulativeTotal,DaysWithSales,0)

3.

TestSales =

IF([Revenue] <= 0, [Budget revenue] + [Avg. daily run rate 2], [Revenue])

4.

Cumulative Sales 4 =

VAR CumulativeTotal = CALCULATE([TestSales],

FILTER(ALLSELECTED('Date'), 'Date'[Date] <= MAX('Date'[Date]) ) )

RETURN

CumulativeTotal

The result I want is this:

Hope anyone can help?

TestPbixFile.pbix

Best regards

Jonas

[lbendlin]

Can't have Nulls in a calendar.

Next step is to correct the relationship.

Having the budget in the calendar table is not optimal. Ideally that should be its own fact table.

Then you can start recreating your measures as needed.  I don't follow your logic entirely so here's only an example for the runrate:

Daily Runrate = 
var d=max(Dates[Date])
return CALCULATE(sumx(Facts,Facts[Realised]),REMOVEFILTERS(Dates[Date]),Dates[Date]<=d) - CALCULATE(sumx(Dates,Dates[Budget]),REMOVEFILTERS(Dates[Date]),Dates[Date]<=d)

see attached

[Jonas_Holm]

This doesn't calculate the average of the difference of actual difference between actual and budget and calculate the run rate based on that.

In the screenshot of the excel spreadsheet.

Calculated run rate 2 is correct, but I want the accumulated total in the column "Result".

[lbendlin]

you lost me at 

 the average of the difference of actual difference between actual and budget and calculate the run rate based on that.

Can you walk me through the required steps?

[Jonas_Holm]

Yes I'll try my best.

In this table, the matrix called avg. daily run rate, is the difference between realised and budget figures. From the 20th of February, I need to calculate the average of all the days, that hasn't any revenue. Which is from the 1st of February until the 19th of February. Which gives 165 in average.

That average amount needs to be split evenly on the remaining days per day.

So when we have the 20th of February, with a budget on 940, we need to subtract 165 on that day. So it gives 940 - 165 = 775 and so forth.

After this, I would like to have the figures as a running total.

So all in all.

1st of Februray -> 19th of February = Realisered figures.

20th of February -> 28th of February = Budget figures subtracted by the average amount.

Did that make sense?

[lbendlin]

don't ask me if this makes sense but here's my version.

[Jonas_Holm]

Hello Ibendlin,

I forgot to say, that the formula should only take into consideration of the current month. Since I want to use it for the future months as well.

I have added some more data in the file, where I added January figures as well, so we have to filter the result of January away - can you fix that, because then you have solved my problem.

TestPbixFile.pbix

So we still get your result from your previous file, but when there is historical data.

Best regards

Jonas

[lbendlin]

no access

[Jonas_Holm]

Try this instead

TestPbixFile.pbix

[lbendlin · Accepted solution]

see attached

[Jonas_Holm]

Hi again,

MUCH appreciated, thanks alot! 

This works.

Best Regards

Jonas

[Jonas_Holm]

Hello Ibendlin,

In the sample it works perfectly, I'll let you know, if I get it to work in my production database! Thanks for this.

Best regards 

Jonas