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

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

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

    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. 

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

      joshlwulf 

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

      Regards

      Suhel