Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to find changes in data from several dates

Hi,   I am trying to figure out how find changes from one date to another, where the dates are in the same column. The change should only register changes from the same ID as the date prior.   ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you Greg_Deckler , this pretty much solved it.

I added some more to also filter by ID, see below

Column =
VAR __Current = [Value]
VAR ID2 = [ID]
VAR PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR Previous = MAXX(FILTER('Table',[Date]=PreviousDate && [ID]=ID2),[Value])
RETURN
__Current - Previous
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Awesome. Yeah, I believe the full MTBF article did something similar. It is a fairly common pattern.