Forum Discussion
Date Difference
- 3 years ago
Srinivas_Itech So basically MTBF. If you really need them in the exact order, then you will need an index (second example). PBIX is attached below signature.
Diff Days = VAR __LogDate = [Log Date] VAR __Order = [Order ID] VAR __PrevLogDate = MAXX(FILTER('Table',[Order ID] = __Order && [Log Date] < __LogDate),[Log Date]) VAR __PrevDate = MAXX(FILTER('Table',[Order ID] = __Order && [Log Date] = __PrevLogDate),[Close Date]) RETURN DATEDIFF(__PrevDate, __LogDate, DAY)Diff Days 2 = VAR __LogDate = [Log Date] VAR __Index = [Index] VAR __Order = [Order ID] VAR __Prev = MAXX(FILTER('Table',[Order ID] = __Order && [Index] < __Index),[Index]) VAR __PrevDate = MAXX(FILTER('Table',[Order ID] = __Order && [Index] = __Prev),[Close Date]) RETURN DATEDIFF(__PrevDate, __LogDate, DAY)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
Srinivas_Itech So basically MTBF. If you really need them in the exact order, then you will need an index (second example). PBIX is attached below signature.
Diff Days =
VAR __LogDate = [Log Date]
VAR __Order = [Order ID]
VAR __PrevLogDate = MAXX(FILTER('Table',[Order ID] = __Order && [Log Date] < __LogDate),[Log Date])
VAR __PrevDate = MAXX(FILTER('Table',[Order ID] = __Order && [Log Date] = __PrevLogDate),[Close Date])
RETURN
DATEDIFF(__PrevDate, __LogDate, DAY)
Diff Days 2 =
VAR __LogDate = [Log Date]
VAR __Index = [Index]
VAR __Order = [Order ID]
VAR __Prev = MAXX(FILTER('Table',[Order ID] = __Order && [Index] < __Index),[Index])
VAR __PrevDate = MAXX(FILTER('Table',[Order ID] = __Order && [Index] = __Prev),[Close Date])
RETURN
DATEDIFF(__PrevDate, __LogDate, DAY)
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
Awesome Sir thanks a lot sir