Data Model Pattern: Date Clamp
Date Clamps
Sometimes it's convenient to have a menu of time periods to choose from, without having to do mental maths about it.
Something like this:
Time Periods |
---|
This Week |
Last Week |
This Month |
Last 4 Weeks |
This FY |
You'll notice that "this week" and "last week" will overlap with "this month". The other thing is that there is no natural to sort them - alphabetical makes no sense, and there's no leading number. You need a separate column to explicitly sort them by.
It's true Power BI has a built-in component that handles this time intelligence fairly well, but sometimes the UX calls for something that is quick and discrete.
The other benefit is that you can have columns going across the top of a matrix table using the time periods, allowing for quick side-by-side comparison.
Value | This Week | Last Week | This Month | Last 4 Weeks | This FY |
---|---|---|---|---|---|
This category | # | # | # | # | # |
That category | # | # | # | # | # |
My way of handling this involves a few components. This article will cover a lot of "time periods", but the idea is definitely not to use them all - if you're trying to cover all cases, just use a date slicer. However, if there are a few discrete time periods that are always being reported on, these will be very handy for the use.
The selection of time periods here is fixed. It takes quite a bit of work to figure out how to arrange the overlapping periods in a way that mechanically works.
Tables
today
table
We need a reference table with today's date (as of the last refresh) and some reference dates, like the start of the week, etc.
The resulting table will look like this:
today | startofweek | endofweek | startofmonth | endofmonth | startoflastmonth | endoflastmonth | startfinancialyear | endoffinancialyear |
---|---|---|---|---|---|---|---|---|
... | ... | ... | ... | ... | ... | ... | ... | ... |
let
dateToday = Date.From(DateTime.LocalNow()),
month = Date.Month(dateToday),
year = Date.Year(dateToday),
today = Table.FromRecords({
[
today = dateToday
]
}),
q1 = { 7 .. 9 },
q2 = { 10 .. 12 },
q3 = { 1 .. 3 },
q4 = { 4 .. 6 },
h1 = q1 & q2, // first half of financial year
h2 = q3 & q4, // second half of financial year
#"Added Custom" = Table.AddColumn( today, "startofweek", each Date.StartOfWeek([today]), type date ),
#"Added Custom1" = Table.AddColumn( #"Added Custom", "endofweek", each Date.EndOfWeek([today]), type date ),
#"Added Custom2" = Table.AddColumn( #"Added Custom1", "startofmonth", each Date.StartOfMonth([today]), type date ),
#"Added Custom3" = Table.AddColumn( #"Added Custom2", "endofmonth", each Date.EndOfMonth([today]), type date ),
#"Added Custom4" = Table.AddColumn( #"Added Custom3", "startoflastmonth", each Date.AddMonths([startofmonth], -1), type date ),
#"Added Custom5" = Table.AddColumn( #"Added Custom4", "endoflastmonth", each Date.EndOfMonth([startoflastmonth]), type date ),
#"Added Custom6" = Table.AddColumn( #"Added Custom5", "startfinancialyear", each if List.Contains(h1, month) then #date(year, 07, 01) else #date(year - 1, 07, 01), type date ),
#"Added Custom7" = Table.AddColumn( #"Added Custom6", "endoffinancialyear", each if List.Contains(h1, month) then #date(year + 1, 06, 30) else #date(year, 06, 30), type date ),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom7",{{"today", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"startfinancialyear", "startoffinancialyear"}})
in
#"Renamed Columns"
Date Clamp
We also need a table that defines the date clamps. This is the table where our slicers will pick up clamp time periods from, and will define the order that they appear in.
The lists of clamp numbers are derived from the calculated column in the next section.
let
Source = {
[ Period = "This Week", Clamp Number = { 1, 2 } ],
[ Period = "Last Week", Clamp Number = { 3, 4 } ],
[ Period = "This Month", Clamp Number = { 1, 3, 5, 7} ],
[ Period = "Last 4 Weeks", Clamp Number = { 1, 3, 4, 5, 6 } ],
[ Period = "This FY", Clamp Number = { 1, 3, 5, 7, 8 } ]
},
toTable = Table.FromRecords(Source),
#"Added Index" = Table.AddIndexColumn(toTable, "Order", 1, 1, Int64.Type),
#"Expanded Clamp Number" = Table.ExpandListColumn(#"Added Index", "Clamp Number"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Clamp Number",{{"Period", type text}, {"Clamp Number", Int64.Type}})
in
#"Changed Type"
Calculated Columns
On the table that is being filtered, we need a column called "Clamp Number". This column will relate to Date Clamp[Clamp Number]
, in a many-to-many relationship.
The measures [Start of This Week]
etc. are defined in the section below.
Clamp Number =
var _date = [createdon]
VAR thisweek = [Start of This Week] <= _date && _date <= [Start of This Week] + 6 && _date <= [_today]
VAR lastweek = [Start of This Week] - 7 <= _date && _date <= [Start of This Week] + 6 - 7
VAR thismonth = [Start of This Month] <= _date && _date <= [End of This Month] && _date <= [_today]
VAR last4weeks = [_today] - 28 < _date && _date <= [_today]
VAR thisfy = [Start of This FY] <= _date && _date <= [_today]
RETURN
SWITCH(
TRUE(),
thisweek && thismonth, 1,
thisweek && NOT(thismonth), 2,
lastweek && thismonth, 3,
lastweek && NOT(thismonth), 4,
thismonth && last4weeks, 5,
last4weeks, 6,
thismonth, 7 ,
thisfy, 8
)
Measures
These measures are based on the table today
.
_today = FIRSTDATE('today'[today])
End of This FY = FIRSTDATE('today'[endoffinancialyear])
End of This Month = FIRSTDATE('today'[endofmonth])
Start of This FY = FIRSTDATE('today'[startoffinancialyear])
Start of This Month = FIRSTDATE('today'[startofmonth])
Start of This Week = FIRSTDATE('today'[startofweek])