Forum Discussion

Jonas_Holm's avatar
Jonas_Holm
Helper I
3 years ago
Solved

Calculated Run Rate Budget vs Actual diff.

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

Need Help

11 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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's avatar
      Jonas_Holm
      Helper I
      3 years ago

      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's avatar
        lbendlin
        Super User
        3 years ago

        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?