Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Icon for Helper V rankHelper V
6 years ago
Solved

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

Regards

Suhel

Need Help
  • joshlwulf's avatar
    joshlwulf
    6 years ago
    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!

     

17 Replies

    • joshlwulf's avatar
      joshlwulf
      Icon for Helper I rankHelper I
      6 years ago

      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. 

    • joshlwulf's avatar
      joshlwulf
      Icon for Helper I rankHelper I
      6 years ago
      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's avatar
      Suhel_Ansari
      Icon for Helper V rankHelper V
      6 years ago

      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

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Suhel_Ansari
      Icon for Helper V rankHelper V
      6 years ago

      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