Forum Discussion
Lag function in DAX
LukeB 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
LukeB 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
Hello Greg_Deckler LukeB,
My 1st column has a list of Equipment, and the 2nd column has the date/time they failed in the date/time format. For each of these equipment, I am trying to find the difference between date/time they failed (difference between rows) and calculate their summation at the end.
I created the Index column and wrote the following DAX code to solve this issue:
DIFF =
var _lasttime = CALCULATE(MAX('Sampling'[BreakDownDate]),FILTER('Sampling','Sampling'[Index]=EARLIER('Sampling'[Index])-1))
return
DATEDIFF(_lasttime,'Sampling'[BreakDownDate],MINUTE)
It is giving me the right number if I just select(filter) just 1 equipment but as I keep adding Equipments to the table it gives me wrong summation of Breakdown date towards the end. As a whole on 2000+ different equipments, my numbers are not that far. But individually for those equipments my numbers are sharply and significantly different for many equipments.
Can you please suggest a better DAX code than above to resolve this issue further?