Date difference between dates in same column

[cpereyra]

Hi All,

I need to calculate the date difference between dates in the same column. I'm using the dax below but it is calculating incorrectly I get all 1s.

Dates Between Prospects =

DATEDIFF (Merge1[Extra_Fields.Log_MS_Date_Started],

FIRSTDATE ( FILTER ( ALL (Merge1[Extra_Fields.Log_MS_Date_Started]), Merge1[Extra_Fields.Log_MS_Date_Started] > EARLIER ( Merge1[Extra_Fields.Log_MS_Date_Started] ))), DAY)

[FrankAT · Accepted solution]

Hi @Greg_Deckler 

i have used a slightly different formula, but your pattern works well:

Difference = 
VAR _CurrentDate = 'Table'[Extra_Fields.Log_MS_Date_Started]
VAR _PreviousDate = 
    MAXX(
        FILTER(
            'Table',
            'Table'[Extra_Fields.Log_MS_Date_Started] < EARLIER('Table'[Extra_Fields.Log_MS_Date_Started])
        ),
        'Table'[Extra_Fields.Log_MS_Date_Started]
    )
RETURN
   IF(_PreviousDate = BLANK(), 0, _CurrentDate - _PreviousDate)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[amitchandak]

@cpereyra , Try a column like

datediff(maxx(filter(table,[date] <earlier([date])),[Date]),[date], day)

or

datediff([date],minx(filter(table,[date] >earlier([date])),[Date]), day)

[cpereyra]

This formula just gives me a result of 1 for all rows.

Days between prospect =

DATEDIFF(MAXX(FILTER(Merge1,Merge1[Extra_Fields.Log_MS_Date_Started]<EARLIER(Merge1[Extra_Fields.Log_MS_Date_Started])),Merge1[Extra_Fields.Log_MS_Date_Started]),Merge1[Extra_Fields.Log_MS_Date_Started],day)

[Greg_Deckler]

@cpereyra 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 __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
  __Current - __Previous

[FrankAT · Accepted solution]

Hi @Greg_Deckler 

i have used a slightly different formula, but your pattern works well:

Difference = 
VAR _CurrentDate = 'Table'[Extra_Fields.Log_MS_Date_Started]
VAR _PreviousDate = 
    MAXX(
        FILTER(
            'Table',
            'Table'[Extra_Fields.Log_MS_Date_Started] < EARLIER('Table'[Extra_Fields.Log_MS_Date_Started])
        ),
        'Table'[Extra_Fields.Log_MS_Date_Started]
    )
RETURN
   IF(_PreviousDate = BLANK(), 0, _CurrentDate - _PreviousDate)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[cpereyra]

It looks like with this formula is picking up the day before rather than the previous date. 

[Greg_Deckler]

Again, 

@cpereyra - If I had to guess, you are probably not including all of the filtering criteria that you need to. Like whatever that field is just to the left of your date field. Your filter criteria needs to include all of the row columns that you want to "group" together.

So, like:

FILTER(
  'Table',
  [Column] = EARLIER([Column]) &&
  [Column1] = EARLIER([Column1])
)

[cpereyra]

Hi, Thank you for getting back. I tried it but still incorrect. Any sugestion ?

[Greg_Deckler · Accepted solution]

@cpereyra - If I had to guess, you are probably not including all of the filtering criteria that you need to. Like whatever that field is just to the left of your date field. Your filter criteria needs to include all of the row columns that you want to "group" together.

So, like:

FILTER(
  'Table',
  [Column] = EARLIER([Column]) &&
  [Column1] = EARLIER([Column1])
)

[cpereyra]

That was it. Thank you so much everyone for all your help. @Greg_Deckler @FrankAT @amitchandak 

[cpereyra]

@Greg_Deckler 

What field would you use as [value]? Do I need a date table besides the date field? 

I'm new to dax. TIA.

[Greg_Deckler]

@cpereyra Depends on what you are trying to lookup, but in your case probably just use [Date] for [Value] in the formula.

[cpereyra]

Result expected between 2/24/2020 and 2/19/2020 is 5 days.