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-
Event11/15/2021 
Event13/15/202159
Event13/17/20212
Event23/1/2021 
Event22/1/2021-28

 

I feel like I have the formula right in this calculated column but for some reason all the results are blank.

 

 

 

 

 

c_DateDiff = 
VAR temp =
    TOPN (
        1,
        FILTER (
            datasource,
            datasource[EventId] = EARLIER ( datasource[EventId] )
                && datasource[Start Date] < EARLIER ( datasource[Start Date] )
        ),
        [End Date], DESC
    )
RETURN
    DATEDIFF ( MINX ( temp, [Start Date] ), datasource[Start Date], DAY )

 

 

 

 

 

Edit: What if I want to calculate the Start Date in relation to the previous/earlier snapshot date?

-EventId--StartDate--Snapshot Date--Desired Result-
Event11/15/20211/1/2021 
Event13/15/20213/1/2021-2
Event13/17/20212/1/202161
Event23/1/20211/1/2021 
Event22/1/20213/1/2021-28
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.

7 Replies

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

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, WorkHard 

     

    Have your problem been solved? I can see from your dax code that you have two tables, but they don't appear in your description, such as 'end date' and 'snapshot date', You need to provide more detail information and the final result you want, then we can help you soon.

     

    Best Regards

    Janey Guo

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    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.