How can I create inclusive time period categories with Dax/PowerBI?

[andres6and8]

I have a data set in Power BI where there's a date column. I'd like to filter the dates along the following criteria: past 30 days, past 60 days, past 90 days, and all time.

I added three custom columns, each with binary values: `is_past_30`, `is_past_60`, and `is_past_90`. I then created a calculation called `Time Frame` and set it to the following:

Time Frame = IF(
        Query1[is_past_30]+Query1[is_past_60]+Query1[is_past_90] < 1, "4. All time"
        , IF(
            Query1[is_past_30]+Query1[is_past_60]+Query1[is_past_90] < 2, "3. Past 90 Days"
            , IF(
                Query1[is_past_30]+Query1[is_past_60]+Query1[is_past_90] < 3, "2. Past 60 Days"
                , IF(
                    Query1[is_past_30]+Query1[is_past_60]+Query1[is_past_90] < 4, "1. Past 30 Days"
                    , "n/a"
    				)
                )
            )
        )

This code works if I want to slice the time periods exclusively- i.e., past 60 days but NOT in the past 30 days. However, by "Last 60 Days" I'd like to include the last 60, too. Under this scheme, probably because of the "IF-THEN" statement, that is not possible when placing the options in a slicer.

Does anyone have a suggestion for a workaround?

[Phil_Seamark · Accepted solution]

Hi @andres6and8

Here is one option that uses Measure tables.  The link to a PBIX file is here

https://1drv.ms/u/s!AtDlC2rep7a-jlM8zvxEyg6XK2MB

Bascially you can add your own date ranges to the table called Date Ranges and these will automatically appear in your slicers.

Use the tab called All Sorts to see what I mean.

[philibin]

If I am understanding your question... I accomplished the same with this calculated column:

let
vnow = Date.From(DateTime.LocalNow())
in
if [Date] = vnow then "TODAY"
else if Date.WeekOfYear([Date]) = Date.WeekOfYear(vnow)
and Date.Year([Date]) = Date.Year(vnow) then "THIS WEEK"
else if Date.ToText([Date], "MM yyyy") = Date.ToText(vnow, "MM yyyy") then "THIS MONTH"
else if Date.QuarterOfYear([Date]) = Date.QuarterOfYear(vnow)
and Date.Year([Date]) = Date.Year(vnow) then "THIS QUARTER"
else if Date.IsInYearToDate([Date]) then "THIS YEAR"
else if Duration.Days(vnow - [Date]) <= 365 then "IN YEAR"
else "ALL TIME"

where the date column I am looking at is called [Date] and I wanted to know if the row is today, in the last week, in the last month, etc...

Then from there I used a table for each group, then appended them all together. It is clunky but works.

[Phil_Seamark · Accepted solution]

Hi @andres6and8

Here is one option that uses Measure tables.  The link to a PBIX file is here

https://1drv.ms/u/s!AtDlC2rep7a-jlM8zvxEyg6XK2MB

Bascially you can add your own date ranges to the table called Date Ranges and these will automatically appear in your slicers.

Use the tab called All Sorts to see what I mean.

[andres6and8]

@Phil_Seamark

Thank you for the example. This is definitely what I'd like to implement, but beside creating a reference table with ID and Time Frame, I'm really not sure how to implement everything else. Could you provide a little more explanation?

Thank you in advance!

[v-sihou-msft · Accepted solution]

@andres6and8

In @Phil_Seamark's pbix, it contains a "Days From Today" column. 

This columns is used to compare with "days" (which is ID in Date Range table) for calculation.

You can also refer to blog below for this kind of "Last X period" requirement:

Power BI – Time Period Slicer for Last 7 Days,Last 30 Days..

Regards,