Forum Discussion

itskool's avatar
itskool
Icon for Advocate II rankAdvocate II
2 years ago

Running total for a measure using two different measures depending on the month

Hi Power BI community, 

 

My situation is following:

I have forecast and actual values, I nees to show actuals for past month and forecast for future month.

For that I created a column in my DimMonths table to identify is the month is in the past or in the future and created measure, which works fine.

Forecast/actuals hours =
IF(
    AVERAGE(DimMonths[Future Month?]) = 0,
    [Actuals hours],
    [Forecast hours w/o generic roles]
    )
 
However, the problem starts when I try to calculate the running total - this measure only sums up the forecast values, also from the past months:
Forecast/actuals hours RT = CALCULATE (
    [Forecast/actuals hours],
    FILTER (
        ALLSELECTED(  DimMonths ),
        DimMonths[Month Sort] <= MAX ( DimMonths[Month Sort] )
    )
)
 
I understand this is because of this part of the first formula: AVERAGE(DimMonths[Future Month?]) = 0,
but I have no idea how to fix it.
 
Does anyone have any ideas or had a similar case?
Need Help

1 Reply