Forum Discussion
Date Difference
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
Hi Sir ,
I have tried but not getting exact value getting very high value please suggest
Srinivas_Itech
Oh! a small mistake in the code (should be "<" not ">"). Apologies for that. Please try
Number of Days =
VAR CurrentLogDate = 'Table'[Log Date]
VAR CurrentIDTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Order ID] ) )
VAR TableBefore =
FILTER ( CurrentIDTable, 'Table'[Log Date] < CurrentLogDate )
VAR PreviousRecord =
TOPN ( 1, TableBefore, 'Table'[Log Date] )
VAR PreviousCloseDate =
MAXX ( PreviousRecord, 'Table'[Close Date] )
RETURN
IF ( NOT ISBLANK ( PreviousCloseDate ), CurrentLogDate - PreviousCloseDate )