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 IV

## DAX Measure Formula to Calcualte Running Fleet

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

Regards

Suhel

2 ACCEPTED SOLUTIONS
Helper I
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!

Helper IV
17 REPLIES 17
Community Support

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.

Helper IV

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

Community Champion

what about running total like this:

Regards FrankAT

Helper I

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.

Super User

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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper IV

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

Helper IV

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

Helper IV

@Greg_Deckler

@TomMartens

Thanks

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

Helper IV

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 🙏

Helper IV
Helper I

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?

Helper IV

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

Helper I

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.

Helper IV

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

Regards

Suhel

Helper IV

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

Helper IV

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

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.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors