Forum Discussion

WorkHard's avatar
WorkHard
Helper V
5 years ago
Solved

Date Difference between two values in the same column with same Id

I'm trying to calculate the difference in days between two values in the same column that have the same id. Consider the following data: -EventId- -Start Date- -Desired Result- Event1 1/15...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, WorkHard 

     

    You can create a column to calculate the dasired result.

    Like this:

     

    column = 
VAR a =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            [-Snapshot Date-] < EARLIER(  'Table'[-Snapshot Date-] )
                && [-EventId-] = EARLIER(  'Table'[-EventId-] )
        ),
        [-Snapshot Date-]
    )
VAR b =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            [-Snapshot Date-] = a
                && [-EventId-] = EARLIER(  'Table'[-EventId-] )
        ),
        [-StartDate-]
    )
RETURN
    DATEDIFF ( b, 'Table'[-StartDate-] , DAY )

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

  • WorkHard's avatar
    WorkHard
    Helper V
    5 years ago

    Thanks. I was hoping for a DAX formula but I'll try to do this in Power Query, like you did. 


    What if I want to calculate the Start Date in relation to the previous snapshot date?
    Check OP for an example.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      You are welcome.  How did you arrive at the numbers in the Desired result column of the second table in your original post?  I am not clear.

      • WorkHard's avatar
        WorkHard
        Helper V
        5 years ago

        Hi Ashish_Mathur ,


        The 3 rows for Event1 come from different points in time. I take a "snapshot" of the data once a month and then every month I add another row for the same event. So over time, the Start Date of the event can change. This gives me the ability to see how the start date for the same event changes every month.

        The Start Date for Event1 in the snapshot from January 2021 was January 15.

        The Start Date for Event 1 in the snapshot from February 2021 was March 17 (61 days).

        The Start Date for Event 1 in the snapshot from March 2021 was March 15 (-2 days)

         

        The Start Date for Event 2 in the snapshot from January 2021 was March 1.

        The Start Date for Event 2 in the snapshot from March 2021 was February 1. (+28 days)