The Preamble

I’m not sure we’ve adopted a universally recognised onomatopoeia for “the Teams tune”. Ring ring is obvs an old skool phone (remember those?) but “do do de do do woop woop” doesn’t really trigger the same imagery, does it?

I digress, and that won’t be the last time today. Now like any self-respecting autistic gen Xer, I hate the phone. And video calls. That Teams in-coming call notification triggers fight, flight, freeze or fawn every time. Teams does chat just fine, there’s no need for all that realtime social interaction thank you very much.

Oh yes, as I was saying, so my other favourite customer called. And I quite like talking to them so after declining the call, I sent them a meeting invite for 15 mins time. A neurodiverse brain thrives on planned events you know, none of these spontaneous unannounced calls.

The Oldest Latest Date

“I need to find the oldest latest date.”

Sometimes unpacking the requirement is the hardest part.

“The oldest latest date?”

“Yes, find the latest dates then the oldest”

“Ah, the min of all the maxes of each group?”

“Yes, the oldest latest”

Alexa, Write Me Some DAX

First up, some test data.

Here’s a list of courses, each course has a number of bookings. We’re looking for the most recent booking per course, and from that, the oldest date. In other words, the date of the last booking for course whose last booking is the oldest.

Or the date the longest inactive course last had a booking.

Test Data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

TABLE Bookings =
    DATATABLE (
        "CourseId", INTEGER,
        "BookingId", INTEGER,
        "Date", DATETIME,
        {
            { 1, 1, "2022-02-15" },
            { 1, 2, "2022-06-01" },
            { 1, 3, "2022-07-30" },
            { 1, 4, "2022-08-03" },
            { 2, 5, "2021-04-01" },
            { 2, 6, "2022-06-15" },
            { 2, 7, "2022-07-23" },
            { 2, 8, "2022-09-01" }
        }
    )

Measures

First, a LastBookingPerCourse measure. Why am I using REMOVEFILTERS instead of ALLEXCEPT? Because Marco knows DAX better than I ever will.

17
18
19
20
21
22
23
24
25

MEASURE Bookings[LastBookingDatePerCourse] =
    VAR Result =
        CALCULATE (
            MAX ( Bookings[Date] ),
            REMOVEFILTERS ( Bookings ),
            VALUES ( Bookings[CourseId] )
        )
    RETURN
        Result

Next, to find the minimum over all courses (which could be filtered by some other table - region or category etc) using:

26
27
28
29
30
31
32

MEASURE Bookings[OldestLastBookingDate] =
    CALCULATE (
        MINX (
            ALL ( Bookings[CourseId] ),
            [LastBookingDatePerCourse]
        )
    )

So simple pretty, once I’d recovered from do do de do do woop woop.

Just The Basic DAX

The DAX is also here