How to get Rolling total for forecast.

[Anonymous]

Hi Team,

 Can you please advise me how to calculate Runway (Rolling Total) in powerbi using DAX like below.

The below highlighed column is achived using Excel spreadsheet with Row No.  I am struggling to get it in powerbi DAX.

Runway by CF for Nov = Total of current forecast - forecast of Nov

Runways by CF for Dec onwards = Runway by CF - forecast of Dec  a and so on....

[Anonymous · Accepted solution]

Thanks Irwan. It works as column measure. For empty value,I can replace it with 0 for last month..right.?

Thanks for this solution buddy 🙂

[SachinNandanwar · Accepted solution]

Its pretty easy. Just change the measure to this

RunAwayByCf = 
VAR _py =
    CALCULATE (
        IF ( SUM ( Data[CurrentForecast] ) = 0, 0, SUM ( Data[CurrentForecast] ) ),
        ALLEXCEPT ( Data, Data[FI Name]),
        MIN(Data[Index] ) <= Data[Index] -1
    )
RETURN
    INT ( _py )

which gives the following o/p. I have sorted it on FI Name so that its easy to validate the results.

[SachinNandanwar · Accepted solution]

Can you check if the DAX you are using is the same as one I posted last time ?



Now I am using the new code and I see no issues.

[SachinNandanwar]

Why are the burn rates for Nov same as July,Aug,Sep&Oct ?

[Anonymous]

because current month and future month should have average of all--as per requirement

[SachinNandanwar]

Could you please mark the previous requirement solved ?

[Anonymous]

HI @SachinNandanwar    I am using below column measure to calculate burn rate.

burnrate1 = IF('Table'[Index]-2>=[CurrentMonthNo],'Table'[Avg],'Table'[Current Cost])

Avg = CALCULATE(AVERAGE('Table'[Current Cost]))

The requiremnt that for current or future month,burn rate is avg of current cost of all months. For past months,its same as current cost of that month as its already passed out.

I hope,this helps 

[SachinNandanwar]

Sorry I dont fully understand your new requirements.

Could you please explain a bit more on how you are calculating Burn rate.Can you show the underlying calculations ?

[SachinNandanwar · Accepted solution]

Its pretty easy. Just change the measure to this

RunAwayByCf = 
VAR _py =
    CALCULATE (
        IF ( SUM ( Data[CurrentForecast] ) = 0, 0, SUM ( Data[CurrentForecast] ) ),
        ALLEXCEPT ( Data, Data[FI Name]),
        MIN(Data[Index] ) <= Data[Index] -1
    )
RETURN
    INT ( _py )

which gives the following o/p. I have sorted it on FI Name so that its easy to validate the results.

[Irwan]

hello @Anonymous 

please check if this accomodate your need.

Runway by CF =
SUMX(
    FILTER(
        'Table',
        'Table'[Index]>EARLIER('Table'[Index])
    ),
    'Table'[Current Forecast]
)

Hope this will help you.

Thank you.

[Anonymous]

Hi @Irwan  your result Runway by CF looks good to me but code is not working for me. Its giving me below error

[Irwan]

@Anonymous 

please use calculated column for that DAX (not measure).

I got same error if I use that DAX in measure.

Thank you

[Anonymous · Accepted solution]

Thanks Irwan. It works as column measure. For empty value,I can replace it with 0 for last month..right.?

Thanks for this solution buddy 🙂

[Anonymous]

Hi @Irwan  I am handling zero manually now.. Your return code giving enexpected syntax error.

[Irwan]

@Anonymous 

You are missing 1 line. Please check carefully first before asking.

Your DAX doesnt have this yellow highlight line.

Thank you.

[Anonymous]

Thanks Irwan. It was not copied somehow during first chance. It worked now...thnx again!!

[Irwan]

@Anonymous 

simple conditional if should do.

Runway by CF =
var _Sum = 
SUMX(
    FILTER(
        'Table',
        'Table'[Index]>EARLIER('Table'[Index])
    ),
    'Table'[Current Forecast]
)
Return
IF(
    ISBLANK(_Sum),
    0,
    _Sum
)

Thank you.

[Anonymous]

Hi @Anonymous ,

You can try formula like below to create measure:

Runway by CF = 
VAR CurrentMonth = MAX('Table'[Index])
VAR TotalForecast = CALCULATE(SUM('Table'[Current Forecast]), ALL('Table'))
VAR RollingTotal = 
    CALCULATE(
        SUM('Table'[Current Forecast]),
        FILTER(
            ALL('Table'),
            'Table'[Index] <= CurrentMonth
        )
    )
RETURN
    TotalForecast - RollingTotal + SUM('Table'[Current Forecast])

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi AdamK,

 This solution doesnt serve the purpose your column Runway by CF is not coming to zero in end.

It should be like below