DAX: Between one and the next

13 Dec, 2024

In this scenario, we have a table of events, and we want to know how far apart the events are from each other, but only with others of the same type. The example here is visits to fast-food restaurants. When was the last time we visited a fast-food restaurant, of the same brand?

Date	Restaurant
01/11/2024	Hungry Jacks
04/11/2024	KFC
10/11/2024	KFC
12/11/2024	KFC
30/11/2024	McDonald's
04/12/2024	Hungry Jacks
06/12/2024	Hungry Jacks
19/11/2024	Subway
25/11/2024	KFC

Here is the code to generate the table, to follow along with:

Code


let
    generate = #table(
        {"Date","Restaurant"},
        {
            {"1/11/2024", "Hungry Jacks"},
            {"4/11/2024", "KFC"},
            {"10/11/2024", "KFC"},
            {"12/11/2024", "KFC"},
            {"30/11/2024", "McDonald's"},
            {"4/12/2024", "Hungry Jacks"},
            {"6/12/2024", "Hungry Jacks"},
            {"19/11/2024", "Subway"},
            {"25/11/2024", "KFC"}
        }
    ),
    setTypes = Table.TransformColumnTypes(generate,{{"Date", type date}, {"Restaurant", type text}}),
    dining = setTypes
in
    dining

This can be done in DAX. The philosophy is to rank each visit where the earliest visit is the most recent, to that brand of restaurant. For each visit, to get the date of the previous visit, we look at the entry where the rank = thisRank - 1.

Rank by Restaurant = 
    VAR thisOne = FIRSTNONBLANK(dining[Restaurant], [Restaurant])

    RETURN
        IF(
            HASONEVALUE(dining[Restaurant]),
            RANKX(
                FILTER(
                    ALLSELECTED(dining),
                    [Restaurant] = thisOne
                ),
                FIRSTDATE(dining[Date]),
                , // skip
                ASC
            )
        )

Previous Visit = 
    VAR thisOne = FIRSTNONBLANK(dining[Restaurant], [Restaurant])
    VAR thisVisit = FIRSTDATE(dining[Date])
    VAR thisRank = [Rank by Restaurant]
    VAR prevVisit = CALCULATE(
        LASTDATE(dining[Date]),
        FILTER(
            ALLSELECTED(dining),
            [Restaurant] = thisOne
            && [Rank by Restaurant] = thisRank - 1 // to get "next visit: [Rank by Restaurant] = thisRank + 1
        )
    )
    RETURN
    prevVisit

To get "days since", get the date in the current context and DATEDIFF with the date of the previous visit. This measure won't result in an aggregate (e.g. average).

Days Since Previous Visit = 
    VAR thisVisit = FIRSTDATE(dining[Date])
    RETURN DATEDIFF([Previous Visit], thisVisit, DAY)

To get the average, do the following:

Average Days Since Previous Visit = 
    AVERAGEX(
        dining,
        [Days Since Previous Visit]
    )

Similarly, to get the longest interval between visits, over the whole table, do the following:

Longest Interval = MAXX(
    dining, 
    [Days Since Previous Visit]
)

#dax