Calculate Difference Between Dates in Same Column

[LAYACH]

I am new to Power BI and need some help with a calculated column.  I want to find the difference between the rows of dates in one column.  Each event creates a date stamp, but I want to calculate the gaps between the event dates when nothing happened.

For example, one event has a date of February 2, 2019 and the next has a date of February 5, 2019.  I want to store the difference in the number of Days between these two dates in a new Calculated Column, so I can then do other calculations with the returned number of days.

None of the posts I have seen addresses this exactly.  Any help would be most appreciated.  Thank you.

[Nolock]

Hi @LAYACH,

try the following DAX measure:

Diff to last = 
VAR lastFoundDate = CALCULATE(
    MAX(Table1[Dates]);
    FILTER(
        ALL(Table1);
        Table1[Dates] < EARLIER(Table1[Dates])
    )
    )
VAR diffInDays = DATEDIFF(Table1[Dates]; lastFoundDate; DAY)
RETURN diffInDays

In Power BI with the result:

[LAYACH]

Thank you, Nolock.

Here is what I tried:

Days Btwn Incidents =
VAR lastFoundDate = CALCULATE(
MAX('PCM Data 2018'[END_DT]);
FILTER(
ALL(PCM Data 2018;
'PCM Data 2018'[END_DT] < EARLIER('PCM Data 2018'[END_DT])
)
)
VAR diffInDays = DATEDIFF('PCM Data 2018'[END_DT]; lastFoundDate; DAY)
RETURN diffInDays

It gives me an error for the syntax though.  I will take your formula above and see if I can get some help with the syntax issue tomorrow in the office.  Thank you.

[Nolock · Accepted solution]

Hi @LAYACH,

if you don't see a mistake in your code, use e.g. Power BI DAX editor or https://www.daxformatter.com/ for formatting and checking the syntax.

In your code was a small typo on the line with ALL:

before: ALL(PCM Data 2018;

after: ALL ( 'PCM Data 2018' );

If you need to replace all semicolons with commas (US, UK), you can also use the earlier mentioned dax formatter.

Days Btwn Incidents =
VAR lastFoundDate =
    CALCULATE (
        MAX ( 'PCM Data 2018'[END_DT] );
        FILTER (
            ALL ( 'PCM Data 2018' );
            'PCM Data 2018'[END_DT] < EARLIER ( 'PCM Data 2018'[END_DT] )
        )
    )
VAR diffInDays =
    DATEDIFF ( 'PCM Data 2018'[END_DT]; lastFoundDate; DAY )
RETURN
    diffInDays