Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Helper V
6 years ago

Adding previous Rows values

Hi All,

Hey DAX Expert i need your help in measure to calculate the value i have 3 measure as mentioned below.

 First Measure = "1 Registration"

Second Measure = "1 Termination"

Third Measure = "3 Growth" which is "3 Growth = [1 Registration] - [1 Termination]"

I was to create a Measure called "Running Pre Values"

The Calcualtion for  Running Pre Values are as mentioned below.

2016 Running Pre Values = 2016 growth

2017 Running Pre Values = 2016 Running Pre Values + 2017 growth

2018 Running Pre Values = 2017 Running Pre Values + 2018 growth

2019 Running Pre Values = 2018 Running Pre Values + 2019 growth

2020 Running Pre Values = 2019 Running Pre Values + 2020 growth

Please help me. Thanks

 

 

Regards

Suhel

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Suhel_Ansari , You can this year and last year values like this, make sure you have YEAR in separate Table or Date Table

     

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    With help of this you can create other calculations

  • Suhel_Ansari's avatar
    Suhel_Ansari
    Helper V
    6 years ago

    amitchandak 

    Thank you for your prompt response, the solution is not giving me the end result what I what I am looking i have explanied ine the below screenshot. Please help me I one formula below however it's not giving the result. Thanks

    Formula => Running Fleet =
            IF(
                MIN([Date].[Year]) <= CALCULATE(MAX([Date].[Year]),ALL('Fact Contract')),
                CALCULATE(([Registrations]-[Terminations]),FILTER(ALL('Fact Contract'[Date].[Year]),'Fact Contract'[Date].[Year]<=MAX(('Fact Contract'[Date].[Year])))),BLANK())--- it's not working 

     

     

    Please assit. Thanks

    Regards

    Suhel

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Suhel_Ansari , Better move out the year in a separate table, like second formula.

       

      Formula => Running Fleet =
      CALCULATE(([Registrations]-[Terminations]),
      FILTER(ALL('Fact Contract'),'Fact Contract'[Date].[Year]<=MAX('Fact Contract'[Date].[Year])))
      or
      Formula => Running Fleet =
      CALCULATE(([Registrations]-[Terminations]),
      FILTER(ALL('Date'),'Date'[Year]<=MAX('Date'[Year])))

       

      You can add if as per need