Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Suhel_Ansari
Helper IV
Helper IV

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

 

Untitled.png

 

Regards

Suhel

7 REPLIES 7
Suhel_Ansari
Helper IV
Helper IV

@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 

 

Result.PNG

 

Please assit. Thanks

Regards

Suhel

@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

@amitchandak 

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

 

NotWorking.png

Regards

Suhel

@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

@amitchandak 

 

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

 

Reg-Ter.pngrel.png

@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
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.