Forum Discussion

LAYACH's avatar
LAYACH
Regular Visitor
7 years ago
Solved

Calculate Difference Between Dates in Same Column

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...
  • Nolock's avatar
    Nolock
    7 years ago

    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

     

Nolock's avatar
Nolock
Resident Rockstar
7 years ago

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's avatar
LAYACH
Regular Visitor
7 years ago

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's avatar
    Nolock
    Resident Rockstar
    7 years ago

    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