topic How to get Rolling total for forecast. in DAX Commands and Tips

How to get Rolling total for forecast.

Anonymous — Mon, 22 Jul 2024 20:14:29 GMT

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....

Re: How to get Rolling total for forecast.

Anonymous — Tue, 23 Jul 2024 01:32:54 GMT

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.

Re: How to get Rolling total for forecast.

Irwan — Tue, 23 Jul 2024 01:36:20 GMT

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.

Re: How to get Rolling total for forecast.

Anonymous — Tue, 23 Jul 2024 02:26:32 GMT

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

Re: How to get Rolling total for forecast.

Anonymous — Tue, 23 Jul 2024 02:31:16 GMT

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

Re: How to get Rolling total for forecast.

Irwan — Tue, 23 Jul 2024 02:41:29 GMT

Anonymous

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

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

Thank you

Re: How to get Rolling total for forecast.

Anonymous — Tue, 23 Jul 2024 02:46:18 GMT

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 🙂

Re: How to get Rolling total for forecast.

Irwan — Tue, 23 Jul 2024 03:27:14 GMT

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.

Re: How to get Rolling total for forecast.

Anonymous — Tue, 23 Jul 2024 17:38:14 GMT

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

Re: How to get Rolling total for forecast.

Irwan — Wed, 24 Jul 2024 01:15:47 GMT

Anonymous

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

Your DAX doesnt have this yellow highlight line.

Thank you.

Re: How to get Rolling total for forecast.

SachinNandanwar — Wed, 24 Jul 2024 10:00:40 GMT

Incase if you want the calculation through a measure

RunAwayByCf = VAR _py = CALCULATE ( IF ( SUM ( Data[CurrentForecast] ) = 0, 0, SUM ( Data[CurrentForecast] ) ), ALLEXCEPT ( Data, Data[Index] ), MAX ( Data[Index] ) < ( Data[Index] ) ) RETURN INT ( _py )

Re: How to get Rolling total for forecast.

Anonymous — Wed, 24 Jul 2024 14:03:15 GMT

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

Re: How to get Rolling total for forecast.

Anonymous — Wed, 24 Jul 2024 18:49:34 GMT

Hi SachinNandanwar and Irwan Thansk for your solution as well. It works as a measure. Now,this code works for me when I have only 3 columns like Index,Month and Current Cost for FI name "CCC" i.e single excel file having only CCC data.

However, when I use actual file which has same columns categorized into different FI Name..Thsi code doesnt work..Its giving wrong values in Runway. Can youn please suggest how to use this code for different values of FI for same month and monthly runway. This mentioned runway has wrong values now.

Index	Month	FI Name	Sum of Current Cost	Runway
1	Nov	CCC	$218,228	5346154
1	Nov	BBB	$3,536	5346154
1	Nov	AAA	$210,935	5346154
2	Dec	CCC	$159,400	5073944
2	Dec	BBB	$3,097	5073944
2	Dec	AAA	$109,713	5073944
3	Jan	CCC	$190,645	4700108
3	Jan	BBB	$15,464	4700108
3	Jan	AAA	$167,727	4700108
4	Feb	CCC	$183,136	4356710
4	Feb	BBB	$13,541	4356710
4	Feb	AAA	$146,721	4356710
5	Mar	CCC	$193,967	3973667
5	Mar	BBB	$19,176	3973667
5	Mar	AAA	$169,900	3973667
6	Apr	CCC	$229,776	3530976
6	Apr	BBB	$24,984	3530976
6	Apr	AAA	$187,931	3530976
7	May	CCC	$283,923	3072933
7	May	BBB	$12,844	3072933
7	May	AAA	$161,276	3072933
8	Jun	CCC	$257,771	2580875
8	Jun	BBB	$39,147	2580875
8	Jun	AAA	$195,140	2580875
9	Jul	CCC	$280,560	2027297
9	Jul	BBB	$57,266	2027297
9	Jul	AAA	$215,752	2027297
10	Aug	CCC	$336,528	1374383
10	Aug	BBB	$73,255	1374383
10	Aug	AAA	$243,131	1374383
11	Sep	CCC	$337,392	716991
11	Sep	BBB	$88,453	716991
11	Sep	AAA	$231,547	716991
12	Oct	CCC	$366,260	0
12	Oct	BBB	$94,059	0
12	Oct	AAA	$256,672	0

Re: How to get Rolling total for forecast.

SachinNandanwar — Thu, 25 Jul 2024 10:14:31 GMT

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.

Re: How to get Rolling total for forecast.

Anonymous — Thu, 25 Jul 2024 16:54:14 GMT

Hi SachinNandanwar Thanks for your response.It really great and wrking for me now...

One last thing which I am struggling and appreciate if you can help me. I need to calculate 2 columns based on current cost and runway for AAA,BBB and CCC.

BurnRate logic is if any current or future month,its avg of current cost otherwise current cost of that month.

I am using below column measure and its working only for CCC and not for other.as its taking avg of everything in that column.

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

Runway by Burn Rate calculated in excel
Nov =SUM(C:C)-C2
Dec onwards = F2-E2,F3-E3,F4-E4

Expected result should show like this .

For CCC =--

Index	Month	Sum of current cost	Runway by CC	Burn Rate	Runway by Burn Rate
1	Nov	218228	2819358	253132.2	2819358
2	Dec	159400	2659958	218228	2566225.833
3	Jan	190645	2469313	190645	2347997.833
4	Feb	183136	2286177	183136	2157352.833
5	Mar	193967	2092210	193967	1974216.833
6	Apr	229776	1862434	229776	1780249.833
7	May	283923	1578511	283923	1550473.833
8	Jun	257771	1320740	257771	1266550.833
9	Jul	280560	1040180	253132.2	1008779.833
10	Aug	336528	703652	253132.2	755647.6667
11	Sep	337392	366260	253132.2	502515.5
12	Oct	366260	0	253132.2	249383.3333

For BBB---

Index	Month	Sum of current cost	Runway by CC	Burn Rate	Runway by Burn Rate
1	Nov	3536	441286	37068.5	441286
2	Dec	3097	438189	3536	404217.5
3	Jan	15464	422725	15464	400681.5
4	Feb	13541	409184	13541	385217.5
5	Mar	19176	390008	19176	371676.5
6	Apr	24984	365024	24984	352500.5
7	May	12844	352180	12844	327516.5
8	Jun	39147	313033	39147	314672.5
9	Jul	57266	255767	37068.5	275525.5
10	Aug	73255	182512	37068.5	238457
11	Sep	88453	94059	37068.5	201388.5
12	Oct	94059	0	37068.5	164320

For AAA -----

Index	Month	Sum of current cost	Runway by CC	Burn Rate	Runway by Burn Rate
1	Nov	210935	2085510	191370.4	2085510
2	Dec	109713	1975797	210935	1894139.583
3	Jan	167727	1808070	167727	1683204.583
4	Feb	146721	1661349	146721	1515477.583
5	Mar	169900	1491449	169900	1368756.583
6	Apr	187931	1303518	187931	1198856.583
7	May	161276	1142242	161276	1010925.583
8	Jun	195140	947102	195140	849649.5833
9	Jul	215752	731350	191370.4	654509.5833
10	Aug	243131	488219	191370.4	463139.1667
11	Sep	231547	256672	191370.4	271768.75
12	Oct	256672	0	191370.4	80398.33333

Expectation is all above FI should work using slicer and give such result..

Re: How to get Rolling total for forecast.

SachinNandanwar — Thu, 25 Jul 2024 17:39:59 GMT

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 ?

Re: How to get Rolling total for forecast.

Anonymous — Thu, 25 Jul 2024 18:16:58 GMT

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

Re: How to get Rolling total for forecast.

SachinNandanwar — Thu, 25 Jul 2024 19:18:16 GMT

This ?

BurnRate = IF ( VALUES ( Data[Index] ) - 2 >= MONTH ( TODAY () ), CALCULATE ( AVERAGE ( Data[CurrentForecast] ), ALLEXCEPT ( Data, Data[FI Name] ) ), VALUES ( Data[CurrentForecast] ) )

Re: How to get Rolling total for forecast.

Anonymous — Thu, 25 Jul 2024 19:34:15 GMT

Hi SachinNandanwar Thanks for ur help. I tried this code as new column,its giving below error

When tried it as new measure,it cant load visual

Re: How to get Rolling total for forecast.

SachinNandanwar — Fri, 26 Jul 2024 07:54:01 GMT

I am not seeing that error at my end. If possible can you share the source file ?