Forum Discussion
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....
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.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.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-E4Expected 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
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- SachinNandanwar
Impactful Individual
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 )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 - SachinNandanwar
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.
IrwanSuper User
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.