Forum Discussion
Date Difference
Hi All ,
I have a issue iam trying to take date diff from 2 date columns based on 1 value but iam getting error kindly help to resolve the issue
Thank's in advance
EX :
| Order ID | Log Date | Close Date | Date_Diff |
| Temp_001 | 01/01/2022 | 10/01/2022 | 0 |
| Temp_001 | 05/02/2022 | 15/02/2022 | |
| Temp_002 | 01/06/2022 | 25/06/2022 | |
| Temp_002 | 02/07/2022 | 10/07/2022 | |
| Temp_002 | 20/07/2022 | 27/07/2022 | |
| Temp_002 | 08/08/2022 | 30/08/2022 | |
| Temp_001 | 20/01/2022 | 25/01/2022 | Required Diff From Close Date (10-01-2022) To Next Log Date (20-01-2022) |
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
9 Replies
Srinivas_Itech Try:
Date_Diff Column = ( [Close Date] - [Log Date] ) * 1.Hi Sir ,
I Tried but i need result as below kindly help sir
Srinivas_Itech So that I understand. For each Order ID, for the last row (last Log Date), you want the difference between the earliest Close date for that Order ID and the last log date for that Order ID, correct?
Hi Sir ,
I need differance between close date to log date differance based on early dates .
Ex : First Log Date = 01-01-2022 First Close Date = 10-01-2022
2nd Log Date = 20-01-2022 2nd Close Date = 30-01-2022
3rd Log date = 10-02-2022 3rd Close Date = 20-02-2022
now need differance between "First Close Date" - "2nd Log Date" in 2nd column
And "2nd close date" - "3rd close date" in 3rd Column
Kindly help sir
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 )*Update
The solution is updated with a small correction to blank out first date
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 tryNumber 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 )
