Forum Discussion

AmrZahran's avatar
AmrZahran
New Member
6 years ago

Difference between Latest Date and 2nd Latest Date

Hello everyone, I am trying to calculate the difference between the data from the latest date and data from the 2nd latest date.   Here is how my data looks like:   Material Requirements ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

AmrZahran - This is a variation of Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

 

Measure = 
  VAR __LatestDate = MAX('Table'[Date])
  VAR __2ndLatestDate = MAXX(FILTER('Table',[Date] <> __LatestDate),[Date])
  VAR __LatestRequirements = SUMX(FILTER('Table',[Date] = __LatestDate),[Requirements])
  VAR __2ndLatestRequirements = SUMX(FILTER('Table',[Date] = __2ndLatestDate),[Requirements])
RETURN
  __LatestRequirements - __2ndLatestRequirements
AmrZahran's avatar
AmrZahran
New Member
6 years ago

Thanks a lot Greg_Deckler for your response. Unfortunately there was something that I miss-typed in the description. I want to calculate the difference in requirements per product (as shown in the updated thread).

Would that still work? Also I have some concerns as what if there is product C which exists only in one of the 2 dates? Would the missing value be assumed as ZERO and the difference still calculated? 

Thank you

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    AmrZahran - Yes, it should work by Product, that's just filter context that is added when doing the calculation so yes, it should work. If there is a product C that only exists for one date, that will be an issue. However there are ways around that. For example, you can do a check if something is blank and do something else. Not sure what you want in that particular instance.