Minimum Difference between 2 dates

[ngadiez]

Hi all,

I have a table

John	20180412
Dave	20180131
Emily	20171226
John	20180205
Emily	20180314
Dave	20180405
John	20180218
John	20180125
Dave	20180401
Emily	20180222

to

Name	Min Difference (days)
John	11
Dave	4
Emily	20

I am trying to use SUMX and SUMMARIZE but to not avail.

Is there any other way to get this? Using Calculated Column or Measure better?

@Greg_Deckler, @MattAllington @Phil_Seamark @quentin_vigne

[Anonymous · Accepted solution]

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.

[v-jiascu-msft · Accepted solution]

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]

Hi @ngadiez,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

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

[OwenAuger · Accepted solution]

@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

[OwenAuger]

Hi @ngadiez

Here's another version, just a measure:

(PBIX link for reference)

Min Difference (days) = 
VAR DateList =
    VALUES ( Data[Date] )
RETURN
    MINX (
        GENERATE (
            SELECTCOLUMNS ( DateList, "Date1", Data[Date] ),
            SELECTCOLUMNS ( EXCEPT ( DateList, { [Date1] } ), "Date2", Data[Date] )
        ),
        ABS ( [Date1] - [Date2] )
    )

EDIT: Possible improvement if we restrict Date2 > Date1:

Min Difference (days) v2 = 
VAR DateList =
    VALUES ( Data[Date] )
RETURN
    MINX (
        GENERATE (
            SELECTCOLUMNS ( DateList, "Date1", Data[Date] ),
            SELECTCOLUMNS ( FILTER ( DateList, Data[Date] > [Date1] ), "Date2", Data[Date] )
        ),
        ABS ( [Date1] - [Date2] )
    )

Note these assume there are no repeated dates, so the difference is the minimum difference between distinct dates.

Regards,

Owen

[Anonymous · Accepted solution]

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.