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

[WorkHard]

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/2021
Event1	3/15/2021	59
Event1	3/17/2021	2
Event2	3/1/2021
Event2	2/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-
Event1	1/15/2021	1/1/2021
Event1	3/15/2021	3/1/2021	-2
Event1	3/17/2021	2/1/2021	61
Event2	3/1/2021	1/1/2021
Event2	2/1/2021	3/1/2021	-28

[v-janeyg-msft · Accepted solution]

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.

[v-janeyg-msft]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[WorkHard]

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]

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]

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)

[Ashish_Mathur]

Hi,

This calculated column formula works

if(ISBLANK(LOOKUPVALUE(Data[StartDate],Data[EventId],Data[EventId],Data[Snapshot Date],CALCULATE(MAX(Data[Snapshot Date]),FILTER(Data,Data[EventId]=EARLIER(Data[EventId])&&Data[Snapshot Date]<EARLIER(Data[Snapshot Date]))))),BLANK(),1*(Data[StartDate]-LOOKUPVALUE(Data[StartDate],Data[EventId],Data[EventId],Data[Snapshot Date],CALCULATE(MAX(Data[Snapshot Date]),FILTER(Data,Data[EventId]=EARLIER(Data[EventId])&&Data[Snapshot Date]<EARLIER(Data[Snapshot Date]))))))

Hope this helps.