Dyota's blog

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])