Adding previous Rows values

[Suhel_Ansari]

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

[Suhel_Ansari]

@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]

@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

[Suhel_Ansari]

@amitchandak 

It's not working as seen in the below image. 

Regards

Suhel

[amitchandak]

@Suhel_Ansari , what is formula for

[Registrations] , [Terminations]

And the Year in visual is also coming from Year Table? Row/group by/Axis should be from Year table

[Suhel_Ansari]

@amitchandak 

Formula are as seen in below screen shot and the tables view as well. Thanks 

[amitchandak]

@Suhel_Ansari , Not a good data model. Unpivot the first table create year a custom column in M of the calculated column in Dax

and use that

https://radacad.com/pivot-and-unpivot-with-power-bi

[amitchandak]

@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