Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
abadi_89
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 CodeCS NameDateValue
CS01Ketchup1/6/20200.946375
CS01Ketchup1/2/20200.953944
CS01Ketchup1/3/20200.981028
CS01Ketchup1/4/20200.971444
CS01Ketchup1/5/20200.9645
CS01Ketchup1/1/20200.955278
CS01Ketchup1/7/20200.923116
CS01Ketchup1/8/20200.814521
CS01Ketchup1/9/20200.809767
CS01Ketchup1/10/20200.778958
CS01Ketchup1/11/20200.750706
CS02Mayonnaise1/1/20201.357695
CS02Mayonnaise1/2/20201.347019
CS02Mayonnaise1/3/20201.338747
CS02Mayonnaise1/4/20201.284948
CS02Mayonnaise1/5/20201.2665
CS02Mayonnaise1/6/20201.266955
CS02Mayonnaise1/7/20201.264721
CS02Mayonnaise1/8/20201.257416
CS02Mayonnaise1/9/20201.271779
CS02Mayonnaise1/10/20201.286299
CS02Mayonnaise1/11/20201.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 CodeCS NameDateValueDiffernce
CS01Ketchup1/11/20200.750706-0.02825
CS02Mayonnaise1/11/20201.2982470.011948

 

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

 

Thank you.

1 ACCEPTED SOLUTION
FrankAT
Community Champion
Community Champion

Hi @abadi_89 

you can di it like this:

 

13-11-_2020_21-41-34.png

 

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)

View solution in original post

2 REPLIES 2
FrankAT
Community Champion
Community Champion

Hi @abadi_89 

you can di it like this:

 

13-11-_2020_21-41-34.png

 

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)

Thank you Frank! simply genius.

Have a good weekend.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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