Date Difference

[Srinivas_Itech]

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)

[tamerj1]

Hi @Srinivas_Itech 

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

[Srinivas_Itech]

Hi Sir ,

I have tried but not getting exact value getting very high value please suggest 

[tamerj1]

@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 )

[Srinivas_Itech]

Hi Sir ,

I Tried but i need result as below kindly help sir 

[Greg_Deckler]

@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?

[Srinivas_Itech]

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 

[Greg_Deckler · Accepted solution]

@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/3395....
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]

Awesome Sir thanks  a lot sir

[Greg_Deckler]

@Srinivas_Itech Try:

Date_Diff Column =
  ( [Close Date] - [Log Date] ) * 1.