new measure which calculates difference between consecutive years.

[Anonymous]

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?

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

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please try this measure:

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

Best regards,

Yuliana Gu

[Anonymous]

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