Forum Discussion

abadi_89's avatar
abadi_89
Frequent Visitor
5 years ago
Solved

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.

Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi abadi_89 

    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

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi abadi_89 

    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)

    • abadi_89's avatar
      abadi_89
      Frequent Visitor
      5 years ago

      Thank you Frank! simply genius.

      Have a good weekend.