Power BI: Time clamps

03 Mar, 2023

Windows of time

I have a Power BI dashboard which shows a figure, over time. This figure is one that changes depending on what time scale you're looking at.

Like many Power BI dashboards, this one started out as pre-existing report. In the old report, they had figures based on

just this week
the last 4 weeks
year-to-date

Now, I could easily throw on a date slicer and call it a day, and it is possible, but it would be nice if they could one-click toggle between those three time windows.

My idea was to have a slicer that had three states on it: "This Week", "4-Week Rolling", and "YTD". I'd have this as a tile slicer.

This takes a few steps to explain. The magic trick will become apparent in the end.

Helper table

My starting point is a helper table that looks like this:

Clamp Number	Period	Order
1	YTD	3
2	YTD	3
3	YTD	3
1	4-Week Rolling	2
2	4-Week Rolling	2
1	This Week	1

Out of this, the columns that are doing the magic are Clamp Number and Period. Below is the DAX to generate this table.

Code


Project Periods Clamp = UNION(
    ROW("Clamp Number", 1, "Period", "YTD", "Order", 3),
    ROW("Clamp Number", 2, "Period", "YTD", "Order", 3),
    ROW("Clamp Number", 3, "Period", "YTD", "Order", 3),
    ROW("Clamp Number", 1, "Period", "4-Week Rolling", "Order", 2),
    ROW("Clamp Number", 2, "Period", "4-Week Rolling", "Order", 2),
    ROW("Clamp Number", 1, "Period", "This Week", "Order", 1)
)


Connecting to the main table
I have a table with a column of dates.
To this table, I add the following column, so that it can connect to the helper clamp table.
Clamp Number = 

VAR latestWeek = MAX(table[Date])

VAR lastFourWeeks =TOPN(4, table, [Date], DESC)

RETURN IF(
    [Date] = latestDate, 
    1, // This Week
    IF(
        [Date] >= MINX(lastFourWeeks, [Date]), 
        2, // 4-Week Rolling
        3 // YTD
    )
)

This means that this week's row will get the clamp number 1. The previous three weeks will get labelled as 2. The rest of them will be labelled 3.
This column Clamp Number connects to the column Clamp Number in the helper table.
Putting it all together
The slicer on the page will slice on Period, so that it will give a choice of "This Week", "4-Week Rolling", and "YTD".
When "YTD" gets clicked, what it will do is filter the main table for all the 1s, 2s, and 3s. Then "4-Week Rolling" is clicked, it will filter only for the 1s and 2s. When "This Week" is clicked, it only filters for the most recent row.

#dax
#powerbi