DAX Measure Formula to Calcualte Running Fleet

[Suhel_Ansari]

Hi DAX Expert,

I need your help to calculate the Running Fleet formula as Measure in Power BI DAX, the calculate as as shown below.

The Date-Year column is a actual Date.

Growth = [Registrations]-[Terminations]

Running Fleet 2016 = Growth
Running Fleet 2017 = Growth+Date 2016
Running Fleet 2018 = Growth+Date 2017
Running Fleet 2019 = Growth+Date 2018
Running Fleet 2020 = Growth+Date 2019

as seen in the below Image. Please help me it's little urgent. Thanks

Regards

Suhel

[joshlwulf · Accepted solution]

Create this measure:
TotalGrowth = CALCULATE ( SUM ( 'Table'[Growth] ) )

And then this one:
Cumulative Growth = IF(MIN([Date-Year])<=CALCULATE(MAX([Date-Year]),ALL('Table')),CALCULATE([TotalGrowth],FILTER(All('Table'[Date-Year]),'Table'[Date-Year]<=MAX(('Table'[Date-Year])))),BLANK())

Let me know if that works for you!

[V-lianl-msft]

Hi @Suhel_Ansari ,

Create running total using  the following DAX formula and check if you get desired result.

RunningTotal = SUMX(FILTER(ALLSELECTED('table'[year]),'table'[year]<=MAX('table'[year])),[Growth])

If the above Dax doesn’t help in your scenario, please help to share sample data of your  table and the formula involved in the measure in the screenshot.

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

[Suhel_Ansari]

Hi Sir,

my Calcaultion are not Running Sum it basically Growth of current year + growth of Last Year.

Let like Growth = [Registration]-[Termination]

Running Fleet 2016 = Growth

Running fleet 2017 = Growth 2017 + Running Fleet 2016

Running fleet 2018 = Growth 2018 + Running Fleet 2017

Running fleet 2019 = Growth 2019 + Running Fleet 2018

Running fleet 2020 = Growth 2020 + Running Fleet 2019

Year column is coming form Date column.

Regards

Suhel

[FrankAT]

Hi @Suhel_Ansari ,

what about running total like this:

 Regards FrankAT

[joshlwulf]

Hi, @Suhel_Ansari , 

@FrankAT gave the most efficient response. If you trim out my if statement, you can copy and paste my measures and get what you need. Please give me and @FrankAT  kudos and mark his as the accepted solution. 

[Greg_Deckler]

So are you saying that the image is what you want or that it is incorrect? What is your source data? I do not understand your formulas " + Date 2016" for example, what is that?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Suhel_Ansari]

@Greg_Deckler  .. The Data Source is SQL View. Thanks

[Suhel_Ansari]

@Greg_Deckler 

Yes I want final measure as Running Fleet 2020 as output the image is just to explain the calculation for my Running Fleet.

so Running Fleet 2016 = Growth , Running Fleet 2017 = Gworth 2016 (last year runnign fleet) + Growth of current year ..

Running Fleet 2018 = Gworth 2017 (last year runnign fleet) + Growth of current year .. Etc.. Hope it's clear.. 🙂 Thanks

Regards

Suhel

[Suhel_Ansari]

Please need your help in Above DAX formula.. 🙏

@Greg_Deckler 

@TomMartens 

Thanks

[joshlwulf · Accepted solution]

Create this measure:
TotalGrowth = CALCULATE ( SUM ( 'Table'[Growth] ) )

And then this one:
Cumulative Growth = IF(MIN([Date-Year])<=CALCULATE(MAX([Date-Year]),ALL('Table')),CALCULATE([TotalGrowth],FILTER(All('Table'[Date-Year]),'Table'[Date-Year]<=MAX(('Table'[Date-Year])))),BLANK())

Let me know if that works for you!

[Suhel_Ansari]

@joshlwulf 

Thank for the reply, the DAX formula which you have is giving the expected result, one thing I want to say the "Registration" , "Termination" & "Growth" are all measure so when I used your formula i am not getting expected as seen in below screen shot,

The Output what I am looking is "Running Fleet" however "Cumulative Growth" is not matching as you can seen below. Please help me 🙏

[Suhel_Ansari · Accepted solution]

@joshlwulf 

[joshlwulf]

Hey, Suhel! Sorry I'm still learning a lot of this so I probably won't be able to help much more, but in your calculate function, shouldn't you add a SUMX in there? 

[Suhel_Ansari]

@joshlwulf 

No worries, Thank you for all your help, i fingered it out the working formula, i did a little change in the formula and same is seen below. Thanks Josh, with your formula the visuals rendering had been reduced from 21300 mili second to 900 mili seconds ....

Formaul

Running Fleet = 
    {IF(MIN([Date].[Year]) <=CALCULATE(MAX([Date].[Year]),ALL(Table)),
            CALCULATE(([Registrations]-[Terminations]),FILTER(ALL(Table[Date].[Year]),
            Table[Date].[Year]<=MAX((Table[Date].[Year])))),BLANK())}

Regards

Suhel

[joshlwulf]

Great to hear, @Suhel_Ansari! I'll definitely keep this post in mind. I could see where this would be really useful in some situations. 

[Suhel_Ansari]

@joshlwulf 

Even I will keep it in my mind for ever.. Thanks for all your prompt response.

Regards

Suhel

[Suhel_Ansari]

@joshlwulf 

Sir It's not working as seen in the below image, in the below image Registrations, Terminations, Growth and _LY all are measure and Year is coming from the Date Colum. the file is 400 MB so i can't share it. Hope below Image can give more information. Looking for the best soltuion for my problem. Thanks

Regards

Suhel

[Suhel_Ansari]

@joshlwulf  .. Thanks Will test it and let you know if it'w working ..