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]-...
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!

     

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

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

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

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

    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 🙏

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

        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?