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.