cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to get Rolling total for forecast.

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

4 ACCEPTED SOLUTIONS
Helper I

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 🙂

Resolver III

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.

Resolver III

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.

Helper I

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
36 REPLIES 36
Resolver III

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 )``````

Helper I

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
Helper I

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

Resolver III

This ?

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

Helper I

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

Resolver III

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

Helper I

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

Resolver III

Hi @ashish_18

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

Helper I

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])
)

Resolver III

Thats very strange.

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

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
Resolver III

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.

Helper I

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
Resolver III

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

Helper I

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

Helper I

HI @SachinNandanwar  any luck in getting Runway by Burn rate

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

Resolver III

Which column does F corresponds to ?

Helper I

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.

Resolver III

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 ?

Helper I

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.