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 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.

Need Help
  • 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

     

3 Replies

  • 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