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 

 

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? 

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

        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