cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mhetre123 Regular Visitor
Regular Visitor

new measure which calculates difference between consecutive years.

Hi,

I have below measure named "Total" and a date column.

Total is addtion of all the columns present in Sheet1.

 

 

Total = SUMX(Sheet1, Sheet1[Ambulances] + Sheet1[Arti.& Multi.Veh] + Sheet1[Autorickshaw] + Sheet1[Cars] + Sheet1[Contract Carriage] + Sheet1[Del.Van (3W)] + Sheet1[Del.Van (4W)] + Sheet1[jeeps] + Sheet1[Mopeds] + Sheet1[Motor Cycle ] + Sheet1[others] + Sheet1[Pvt.Ser.Vehicles] + Sheet1[School buses] + Sheet1[Scooters] + Sheet1[Stage Carriage] + Sheet1[Stn.Wagons] + Sheet1[Tankers] + Sheet1[Taxies Cabs] + Sheet1[Tractors] + Sheet1[Trailers] + Sheet1[Trucks & Lorries])

I need a new measure which calculates difference between consecutive years.

How do I do that?

 

SGPhoto_2018_08_09 13_23_23.jpg

 

Also there are any tutorials to practice such basic calculations which helps to explore and understand various DAX functions?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: new measure which calculates difference between consecutive years.

Hi @Mhetre123,

 

Please try this measure:

difference between consecutive years =
[Total]
    - CALCULATE (
        [Total],
        FILTER ( ALL ( Sheet1 ), Sheet1[Date] = MAX ( Sheet1[Date] ) - 1 )
    )

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: new measure which calculates difference between consecutive years.

Hi @Mhetre123,

 

Please try this measure:

difference between consecutive years =
[Total]
    - CALCULATE (
        [Total],
        FILTER ( ALL ( Sheet1 ), Sheet1[Date] = MAX ( Sheet1[Date] ) - 1 )
    )

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mhetre123 Regular Visitor
Regular Visitor

Re: new measure which calculates difference between consecutive years.

Hi Yuliana,

I am very sorry delayed response.

 

I tried two different measures for calculation. Is it good practice to use different measures? How do I optimize it further?

 

Measure suggested by you is not working for me. I wonder where I went wrong. Could you have a look at my file?

 

https://drive.google.com/open?id=1b4oPzUdB0LWDv6YOgj4ligmJLNTgUcsi