topic Re: Date Difference in DAX Commands and Tips

Date Difference

Srinivas_Itech — Mon, 03 Oct 2022 13:53:22 GMT

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)

Re: Date Difference

Greg_Deckler — Mon, 03 Oct 2022 13:59:09 GMT

Srinivas_Itech Try:

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

Re: Date Difference

Srinivas_Itech — Mon, 03 Oct 2022 14:01:32 GMT

Hi Sir ,

I Tried but i need result as below kindly help sir

Re: Date Difference

Greg_Deckler — Mon, 03 Oct 2022 14:05:34 GMT

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?

Re: Date Difference

Srinivas_Itech — Mon, 03 Oct 2022 14:19:43 GMT

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

Re: Date Difference

tamerj1 — Mon, 03 Oct 2022 14:47:36 GMT

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

Re: Date Difference

Greg_Deckler — Mon, 03 Oct 2022 15:13:34 GMT

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

Re: Date Difference

Srinivas_Itech — Mon, 03 Oct 2022 15:31:47 GMT

Hi Sir ,

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

Re: Date Difference

Srinivas_Itech — Mon, 03 Oct 2022 15:54:18 GMT

Awesome Sir thanks a lot sir

Re: Date Difference

tamerj1 — Mon, 03 Oct 2022 16:06:32 GMT

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 )