Place aging items into historic aging buckets within context of past dates

[powerquest1234]

Background: I am fascinated by this video that counts the number of guests in a hotel by date.

In summary, this DAX coder writes a formula to determine how many guests are staying at a hotel during a particular period. A typical COUNT() formula such as won't quite get us there because it counts how many guests CHECKED IN to the hotel on that day. The coder uses the below formula to get to the number of guests staying at the hotel on a particular date:

Count of Active Events Per Period =

    VAR endDate_visual =

        MAX(Dates[Date])

    VAR startDate_visual =

        MIN(Dates[Date])

    VAR results =

        CALCULATE(

            [Count of Cases],

            REMOVEFILTERS(Dates),

            Data[BeginDate] <= endDate_visual,

            Data[EndDate] >= startDate_visual

            || ISBLANK(Data[EndDate])

        )

    RETURN

        results

This is my question:

Lets say the hotel asked to get "categories" of guest by how many days each guest has been staying at the hotel on a particular day. Lets say every Monday, the hotel confirms how many days each guest has been staying so they can make a Stay Table like so:

Stay Table:

If I add a few columns to the above video's source file "Bookings Data" to show about each guests' current stay as of various dates, the table looks like below:

Stay Snapshot Table:

If you look at the three guests that checked in on 1/7/2021 and checked out on 1/18/2021 in the Stay Snapshot Table, those three guests are counted in the below Stay Table's categories:

- 1/11/2021: There are four days between the check-in date 1/7/2021 and the snapshot date of 1/11/2021. As of 1/11/2021, these three guests had stayed at the hotel for four days. For the 1/11/2021 row on the Stay Table, they would be counted in the 3-5 days bucket.

- 1/18/2021: There are 11 days between the check-in date 1/7/2021 and the snapshot date of 1/18/2021. As of 1/18/2021, these three guests had stayed at the hotel for 11 days. For the 1/18/2021 row on the Stay Table, they would be counted in the  "More than 10 days" bucket.
- 1/25/2021, 2/1/2021, etc: The three guests checked out on 1/18/2021, so they wouldn't be counted in any of the stay categories on the 1/25/2021 row or the 2/1/2021 row. Likewise, these guests had not yet checked in on 1/4/2021, so they aren't included in any of those categories either.

Stay Table:

Is there a way to use DAX to create buckets like the above and divide guests (or aging documents) into the appropriate column according to the date in the visual? This seems like it could be helpful for other uses as well, such as tracking progress in aging debts week-over-week.

[OwenAuger · Accepted solution]

Hi @powerquest1234 

You can use a Dynamic Segmentation approach for this.

I have attached a modified PBIX to illustrate.

I create a segmented measure based on Number of Guests Present at end of Period. 

You may need to adjust exactly how boundaries are handled for the ranges of the buckets, but the principle should remain the same.

For example, since I have used Number of Guests Present at end of Period as the base measure, which doesn't count guests on their checkout date, the 3 guests that checked in on 7 Jan 2021 and checked out on 18 Jan 2021 are not counted on 18 Jan 2021.

The steps I followed to create this are:

1. Create a segmentation table which I called Bucket. Blank Min/Max values are treated as unbounded.
   
2. Modify the existing Number of Guests Present at end of Period measure by adding KEEPFILTERS around the Check in Date and Checkout Date filters (code below).
3. Create a new measure Number of Guests Present at end of Period by Bucket that follows a dynamic segmentation-type pattern (code below). This measure relies on the assumption that buckets are non-overlapping, so uses a SUMX over the Bucket table.
4. Place the new measure in a visual grouped by Bucket Label.

Number of Guests Present at end of Period = 
VAR EndDatePerVisual =
    MAX('Calendar'[Date])
VAR RESULT = 
    CALCULATE (
        [Number of Guests Checked In],
        REMOVEFILTERS('Calendar'),
        KEEPFILTERS ( BookingsData[Check in Date] <= EndDatePerVisual ),
        KEEPFILTERS (
            BookingsData[Checkout Date] > EndDatePerVisual
                || ISBLANK( BookingsData[Checkout Date] )
        )
    )
RETURN
    RESULT

Number of Guests Present at end of Period by Bucket = 
VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR Result =
    -- Aggregate across buckets to allow a total to be calculated across buckets
    SUMX (
        Bucket,
        VAR BucketMin = Bucket[Bucket Min]
        VAR BucketMax = Bucket[Bucket Max]
        VAR DateMin =
            IF ( NOT ISBLANK ( BucketMax ), MaxDate - BucketMax ) -- can be BLANK i.e. unbounded
        VAR DateMax =
            IF ( NOT ISBLANK ( BucketMin ), MaxDate - BucketMin ) -- can be BLANK i.e. unbounded
        VAR CheckInDateFilter =
            DATESBETWEEN ( BookingsData[Check in Date], DateMin, DateMax )
        RETURN
            CALCULATE ( 
                [Number of Guests Present at end of Period],
                KEEPFILTERS ( CheckInDateFilter ) -- KEEPFILTERS in case there happen to be filters on Check in Date
            )
    )
RETURN
    Result

At least this is one approach. Does something similar work for you?

Regards,

[powerquest1234]

That is incredible! Part of my struggle was not sure how to describe what I was looking for. It's amazing that it exists as a DAX Pattern.

I also wanted to add on something of my own for anyone clicking on this topic--lets say you want your table to be a collection of "snapshots" taken on Mondays, but you don't want to filter your data (ie you want your table to have only Monday snapshot-dates of current guests as of that Monday, but you don't want to filter out guests [ie you aren't trying to filter to people who checked in/checked out on Mondays or something, you just want your rows to be Monday-snapshot-dates]). I recommend watching this video for an idea of how to filter a table visual. https://www.youtube.com/watch?v=MYHG-QSM8qw

In my situation, I added a DAX calculated table that filtered the Date table to dates where 'Date'[Date] occurred on a Monday within a specific date range. I connected this table to the Date[Date] table. When creating my visual, I added the dates column from my DAX calculated table to the "Rows" field. I believe I was able to get to my aging using Owen's formula and I was able to filter to my specific "snapshots" using the DAX calculated table.