Minimum Difference between 2 dates

This was a fun one. Took me a while, and I'll bet my solution isn't the prettiest or most efficient, but it works.

For each date, to get the nearest date:

Closest Date = 
VAR previous = CALCULATE(MAX(Data[Date]), FILTER(Data, [Date] < EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name])))
VAR next = CALCULATE(MIN(Data[Date]), FILTER(Data, [Date] > EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name])))
VAR daysFromPrevious = DATEDIFF(previous, Data[Date], DAY)
VAR daysFromNext = DATEDIFF(Data[Date], next, DAY) 
return SWITCH(TRUE(),
    ISBLANK(next), previous,
    ISBLANK(previous), next,
    daysFromNext > daysFromPrevious, previous, next)

Only a few swaps to get the days between that date and  the nearest date:

Days From Closest Date = 
VAR previous = CALCULATE(MAX(Data[Date]), FILTER(Data, [Date] < EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name])))
VAR next = CALCULATE(MIN(Data[Date]), FILTER(Data, [Date] > EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name])))
VAR daysFromPrevious = DATEDIFF(previous, Data[Date], DAY)
VAR daysFromNext = DATEDIFF(Data[Date], next, DAY) 
return SWITCH(TRUE(),
    ISBLANK(next), daysFromPrevious,
    ISBLANK(previous), daysFromNext,
    daysFromNext > daysFromPrevious, daysFromPrevious, daysFromNext)

All that's left is a simple MIN()-based measure:

Minimum Difference = MIN(Data[Days From Closest Date])

Using variables really helps to break this problem down into its simplest components.

Hi ngadiez,

The solution is achieved by "Summarize".

Measure 2 =
MINX (
    SUMMARIZE (
        'Table2',
        Table2[Name],
        Table2[Date],
        "dates", DATEDIFF (
            [Date],
            CALCULATE (
                MIN ( 'Table2'[Date] ),
                FILTER (
                    ALLEXCEPT ( 'Table2', Table2[Name] ),
                    'Table2'[Date] > MIN ( 'Table2'[Date] )
                )
            ),
            DAY
        )
    ),
    [dates]
)

Best Regards,

Dale

v-jiascu-msft

That's very good - we just need to compare each date with the next date!

You've inspired one last version from me (first time I've used NATURALINNERJOIN in a measure):

Min Difference (Days) v3 = 
VAR DateList =
    VALUES ( Data[Date] )
VAR DateListIndexed =
    ADDCOLUMNS ( DateList, "Index", RANKX ( DateList, Data[Date],, ASC ) )
VAR DateListIndexedPlusOne =
    SELECTCOLUMNS ( DateListIndexed, "Date2", Data[Date], "Index", [Index] + 1 )
VAR Joined =
    NATURALINNERJOIN ( DateListIndexed, DateListIndexedPlusOne )
RETURN
    MINX ( Joined, ABS ( [Date] - [Date2] ) )

Regards,

Owen