cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Last 2 months difference measure

Hello all,

This is driving me crazy, I tried alot of stuff but it looks I can't do it.

I have this table:

 CS Code CS Name Date Value CS01 Ketchup 1/6/2020 0.946375 CS01 Ketchup 1/2/2020 0.953944 CS01 Ketchup 1/3/2020 0.981028 CS01 Ketchup 1/4/2020 0.971444 CS01 Ketchup 1/5/2020 0.9645 CS01 Ketchup 1/1/2020 0.955278 CS01 Ketchup 1/7/2020 0.923116 CS01 Ketchup 1/8/2020 0.814521 CS01 Ketchup 1/9/2020 0.809767 CS01 Ketchup 1/10/2020 0.778958 CS01 Ketchup 1/11/2020 0.750706 CS02 Mayonnaise 1/1/2020 1.357695 CS02 Mayonnaise 1/2/2020 1.347019 CS02 Mayonnaise 1/3/2020 1.338747 CS02 Mayonnaise 1/4/2020 1.284948 CS02 Mayonnaise 1/5/2020 1.2665 CS02 Mayonnaise 1/6/2020 1.266955 CS02 Mayonnaise 1/7/2020 1.264721 CS02 Mayonnaise 1/8/2020 1.257416 CS02 Mayonnaise 1/9/2020 1.271779 CS02 Mayonnaise 1/10/2020 1.286299 CS02 Mayonnaise 1/11/2020 1.298247

I need a measure or column that can distinguish between the CS Codes (column 1) and it brings the difference between the last available date and the date before so the results should looks like:

CS01: 0.750706 - 0.778958 = -0.02825

CS02:  1.298247- 1.286299 = 0.011948

 CS Code CS Name Date Value Differnce CS01 Ketchup 1/11/2020 0.750706 -0.02825 CS02 Mayonnaise 1/11/2020 1.298247 0.011948

So it should recognize the last date (blue) and do a minus for the date before.

Thank you.

1 ACCEPTED SOLUTION
Community Champion

you can di it like this:

``````Measure =
VAR _MaxDate = MAX('Table'[Date])
VAR _PreviousDate = CALCULATE(MAX('Table'[Date]),'Table'[Date] < _MaxDate)
VAR _ValueOfMaxDate = CALCULATE(MAX('Table'[Value]),FILTER('Table',_MaxDate = 'Table'[Date]))
VAR _ValueOfPreviousDate = CALCULATE(MAX('Table'[Value]),FILTER('Table',_PreviousDate = 'Table'[Date]))
RETURN
_ValueOfMaxDate - _ValueOfPreviousDate``````

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

2 REPLIES 2
Community Champion

you can di it like this:

``````Measure =
VAR _MaxDate = MAX('Table'[Date])
VAR _PreviousDate = CALCULATE(MAX('Table'[Date]),'Table'[Date] < _MaxDate)
VAR _ValueOfMaxDate = CALCULATE(MAX('Table'[Value]),FILTER('Table',_MaxDate = 'Table'[Date]))
VAR _ValueOfPreviousDate = CALCULATE(MAX('Table'[Value]),FILTER('Table',_PreviousDate = 'Table'[Date]))
RETURN
_ValueOfMaxDate - _ValueOfPreviousDate``````

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Frequent Visitor

Thank you Frank! simply genius.

Have a good weekend.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors