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]

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 )

[Anonymous]

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

[Anonymous]

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

[SachinNandanwar]

This ?

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

[Anonymous]

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

[SachinNandanwar]

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

[Anonymous]

Hi @SachinNandanwar   I have removed unwanted columns from actual source file and this one is export based on which I am calculating burn rate using ur code.

Index	Month	FI Name	Sum of Current Cost	Runway by Test1
1	Nov	CCC M&S	$218,228	2819358
1	Nov	IVR M&S	$3,536	441286
1	Nov	P&V	$210,935	2085510
2	Dec	CCC M&S	$159,400	2659958
2	Dec	IVR M&S	$3,097	438189
2	Dec	P&V	$109,713	1975797
3	Jan	CCC M&S	$190,645	2469313
3	Jan	IVR M&S	$15,464	422725
3	Jan	P&V	$167,727	1808070
4	Feb	CCC M&S	$183,136	2286177
4	Feb	IVR M&S	$13,541	409184
4	Feb	P&V	$146,721	1661349
5	Mar	CCC M&S	$193,967	2092210
5	Mar	IVR M&S	$19,176	390008
5	Mar	P&V	$169,900	1491449
6	Apr	CCC M&S	$229,776	1862434
6	Apr	IVR M&S	$24,984	365024
6	Apr	P&V	$187,931	1303518
7	May	CCC M&S	$283,923	1578511
7	May	IVR M&S	$12,844	352180
7	May	P&V	$161,276	1142242
8	Jun	CCC M&S	$257,771	1320740
8	Jun	IVR M&S	$39,147	313033
8	Jun	P&V	$195,140	947102
9	Jul	CCC M&S	$280,560	1040180
9	Jul	IVR M&S	$57,266	255767
9	Jul	P&V	$215,752	731350
10	Aug	CCC M&S	$336,528	703652
10	Aug	IVR M&S	$73,255	182512
10	Aug	P&V	$243,131	488219
11	Sep	CCC M&S	$337,392	366260
11	Sep	IVR M&S	$88,453	94059
11	Sep	P&V	$231,547	256672
12	Oct	CCC M&S	$366,260	0
12	Oct	IVR M&S	$94,059	0
12	Oct	P&V	$256,672	0

[SachinNandanwar]

Hi @Anonymous 

I tested the query with the data you provided and I am not getting any errors.

[Anonymous]

Hi @SachinNandanwar can you please try using this exact code . Rename column name and table name.  I am creating new column /new measure using this code . Its saying "A table of multiple values was supplied where a single value was expected."

BurnRate =
IF (
VALUES('Current Forecast'[Index]) - 2
>= MONTH (TODAY()),
CALCULATE (
AVERAGE('Current Forecast'[Current Cost]),
ALLEXCEPT('Current Forecast', 'Current Forecast'[FI Name])
),
VALUES ('Current Forecast'[Current Cost])
)

[SachinNandanwar]

Thats very strange.

How about this ?

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

[Anonymous]

Hi @SachinNandanwar   This is good knowledge sharing discussion :).

I have tested with your new/latest  code ,it work with no error but it shows avg value in all fields.

However,I have found out the root cause,I have removed "VALUES" from your earlier code and it works well now. Its strange but it was looking for single value VS multiple table values

BurnRate =
IF (
('Current Forecast'[Index]) - 2
>= MONTH (TODAY()),
CALCULATE (
AVERAGE('Current Forecast'[Current Cost]),
ALLEXCEPT('Current Forecast', 'Current Forecast'[FI Name])
),
('Current Forecast'[Current Cost])
)

This code is working Now.. Thanks for your helping hand.

Now,only blocker is to have runway based on burn rate. I will try using ur runwayCF code but its not giving me expected result. Can you plz try from ur end when feasible

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

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

[Anonymous · Accepted solution]

HI @SachinNandanwar   its working with earlier code itself. 

Do u have any idea how to get Runway by Burn rate

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

[SachinNandanwar]

Should this calculation "Nov =SUM(C:C)-C2" be specific only for the month of Nov ?

[Anonymous]

HI @SachinNandanwar  Yes..Its specific to Nov only. Rest should be like (Dec onwards ) = F2-E2,F3-E3,F4-E4

[Anonymous]

HI @SachinNandanwar  any luck in getting Runway by Burn rate

Nov =SUM(C:C)-C2
Dec onwards = F2-E2,F3-E3,F4-E4

[SachinNandanwar]

Which column does F corresponds to ?

[Anonymous]

HI @SachinNandanwar 

Runway by Burnrate for Nov = Total of current cost - Nov cost i.e.( C:C)-C2
Runway by Burnrate for Dec =Runway by Burnrate for Nov - burn rate of Nov i.e F2-E2
Runway by Burnrate for Jan =Runway by Burnrate for Dec - burn rate of Dec i.e F3-E3
and so on

It should work for any FI liek AAA,BBB,CCC like we did for prev runway.

[SachinNandanwar]

Can you please explain for these 3 rows ?

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

Your requirements are still not clear.
You said "Runway by Burnrate for Nov = Total of current cost - Nov cost i.e.( C:C)-C2"

What does C : C mean here ?

[Anonymous]

Hi @SachinNandanwar 

If excel row number are confusing then u can ignore.. Actual requiremnt is as below.

Runway by Burnrate for Nov = Total of current cost - Nov cost i.e.( C:C)-C2
Runway by Burnrate for Dec =Runway by Burnrate for Nov - burn rate of Nov i.e F2-E2
Runway by Burnrate for Jan =Runway by Burnrate for Dec - burn rate of Dec i.e F3-E3
and so on

(C:C ) means All rows of column "current cost". I taken that to take total of that column and minus current cost of Nov (C2)

Actual excel for "AAA" is as below but in main source file AAA,BBB,CCC are added as column so runway and burn rate shoudl work when we select AAA,BBB,CCC from slicer.