Forum Discussion
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. ...
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
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
Anonymous Awesome. Yeah, I believe the full MTBF article did something similar. It is a fairly common pattern.