Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Suhel_Ansari
Helper IV
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

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

Running Fleet.PNG

Regards

Suhel

2 ACCEPTED SOLUTIONS

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!

 

View solution in original post

17 REPLIES 17
V-lianl-msft
Community Support
Community Support

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.

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
Community Champion
Community Champion

Hi @Suhel_Ansari ,

what about running total like this:

 

16-04-_2020_16-09-47.png

 Regards FrankAT

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
Super User
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?

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

@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
Helper IV
Helper IV

Please need your help in Above DAX formula.. 🙏

 

@Greg_Deckler 

@TomMartens 

 

Thanks

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!

 

@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 🙏

 

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? 

@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

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. 

@joshlwulf 

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

Regards

Suhel

@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

 

Error 2.pngError1.PNG

Regards

Suhel

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors