Power BI - Custom Date Range Filter Support

[ShaneHi]

Hi everyone,

I'm going to start this by saying i'm still very much in the learning phase of my Power BI journey - so bare with me!

My aim is to have a Slicer that will change a data table to show "Last 7 Days", "Last 10 Weeks", "Last 12 Months" or "Custom Date Range" (as an example).

I've actually managed to get a Slicer on 2 of my pages to do part of my aim.

On the first page I used this guide, to build a Date Parameter table that pulls specific columns from my Date Table. The code is as follows:

Date Parameter (MoM Pages) = {
    ("D", NAMEOF('Calendar'[Date]), 0),
    ("W", NAMEOF('Calendar'[W/C Monday]), 1),
    ("M", NAMEOF('Calendar'[Month]), 2)
}

It's fine for the page it's on but obviously the "D" (days) pull through a large amount of data as there's no specific filter on the range.

So, on my second page I built (using this YouTube guide) a slicer that I can customise the data range. The difficulty I'm having is this just reads my "Calendar[Date]" column, so I can't have week totals or month totals on the filter. The code is here:

Date Periods (Day Date Slicer) = 
UNION(
    ADDCOLUMNS(
        DATESMTD('Calendar'[Date]),
        "Type", "Month to Date",
        "Order", 2
),
    ADDCOLUMNS(
        DATESYTD('Calendar'[Date]),
        "Type", "Year to Date",
        "Order", 3
),
    ADDCOLUMNS(
        CALENDAR(MIN('Calendar'[Date]),MAX('Calendar'[Date])),
        "Type", "Custom",
        "Order", 4
),
    ADDCOLUMNS(
       DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date])-1,-7,DAY),
       "Type", "Last 7 Days",
        "Order", 1
),
    ADDCOLUMNS(
       DATESINPERIOD('Calendar'[W/C Monday],MAX('Calendar'[W/C Monday])-1,-3,MONTH),
       "Type", "Last 12 Weeks",
        "Order", 5
)
    )

My aim is to somehow get a combination of both that I can throw onto one slicer. The only work around I can think of is using bookmarks to take to a duplicate page, but I guess thats not the tidiest method!

Any help will be greatly appreciated.

Note: This is a request on a build for my job - so i'm unable to share the Power BI desktop file for confidentiality reasons.

[Anonymous]

Hi @ShaneHi 

Based on your needs, I have created the following tables:

Calendar = CALENDAR(DATE(2022,1,1),DATE(2026,1,1))

Then please try the following Measure and apply the measure to a visualization and modify filter settings:

SelectedPeriod = 
VAR cc =
    SWITCH (
        SELECTEDVALUE ( SlicerOptions[Period] ),
        "Last 7 Days",
            IF (
                MAX ( 'Calendar'[Date] ) >= TODAY()-7
                    && MAX ( 'Calendar'[Date] ) <= TODAY (),
                1
            ),
        "Last 10 Weeks",
            IF (
                MAX ( 'Calendar'[Date] ) >= TODAY()-70
                    && MAX ( 'Calendar'[Date] ) <= TODAY (),
                1
            ),
        "Last 12 Months",
            IF (
                MAX ( 'Calendar'[Date] )
                    >= DATE ( YEAR ( TODAY () ) -1 , MONTH ( TODAY () ) , 1 )
                    && MAX ( 'Calendar'[Date] )
                        <= TODAY(),
                1
            ),
        BLANK ()
    )
RETURN
    IF ( ISFILTERED ( 'SlicerOptions'[Period] ), cc, 1 )

Below are my final results, which I hope meet your expectations:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ShaneHi]

Hi,

Sorry this isn't what i'm looking for 😞

With my Slicer (below):

I would like each option to be linked to a specific column in my Calendar.

• 'Calendar'[Date] = Last 7 Days
• 'Calendar'[W/C Monday] = Last 12 Weeks
• 'Calendar'[Month] = Last 12 Months

So when selecting one of the specific options, my Matrix Table will then update with the specific data for that date range, from the calendar specified. 

From the Last 7 Days option, you'll see the dates listed in a daily format.

For the Last 12 Months, you'll see it's specifically using my formatted Month headers from the calendar

And finally, for the last 12 weeks - you'll again see it's using my specific column for W/C Monday as the headings.

Apologies if my explanations haven't been that great!

In my first post I mentioned two slicers I have on separate pages. The tables above have been created using a combination of both those slicers on one page. I would rather just have it in one slicer with the 3 options listed.

Thank you!

[Anonymous]

Hi @ShaneHi 

To create a single slicer that can handle custom date ranges like "Last 7 Days," "Last 12 Weeks," "Last 12 Months," or "Custom Date Range," you can approach it by building a dynamic table with distinct ranges. Here’s how you can combine the two approaches:

1. Create a Date Range Table:
Build a separate table that defines each time period you want in the slicer (e.g., "Last 7 Days," "Last 12 Weeks," etc.).

Date Ranges =
DATATABLE(
"Range", STRING,
"SortOrder", INTEGER,
{
{"Last 7 Days", 1},
{"Last 10 Weeks", 2},
{"Last 12 Months", 3},
{"Custom", 4}
}
)

2. Modify your Date Filter Logic:
You can then use a 'SWITCH' statement or 'IF' conditions to determine which period should be applied based on the selected slicer value.

FilteredDates =
SWITCH(
SELECTEDVALUE('Date Ranges'[Range]),
"Last 7 Days", DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -7, DAY),
"Last 10 Weeks", DATESINPERIOD('Calendar'[W/C Monday], MAX('Calendar'[W/C Monday]), -10, WEEK),
"Last 12 Months", DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH),
"Custom", CALENDAR(MIN('Calendar'[Date]), MAX('Calendar'[Date])) -- Assuming custom range will be user-driven via other slicers
)

3. Create a Measure for Filtering:
Create a measure to apply the selected date range to your table visuals.

FilteredMeasure =
CALCULATE(
[Your Measure Here],
FilteredDates
)

4. Link Slicer to Date Table:
Use the 'Date Ranges' table you created as a slicer, and link it to the filtering logic above.

5.  Custom Date Range Slicer:

For the "Custom Date Range," you can create a Date range slicer for the start and end date using the regular date slicer on the 'Calendar'[Date] column. You can then handle the logic in the same `SWITCH` case for "Custom"

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NickLiu]

https://www.youtube.com/watch?v=5XNcGL0g-sE&t=2s&ab_channel=NickPowerBi

maybe check out this video that shows step-by-step instructions on how to make a date filter to select Last 90 days, last week, current month, or select any specific date ranges. 

[ShaneHi]

Hey,

Thanks for the response! A Couple of follow up questions:

In Step 2 - "WEEK" isn't an option in DATESINPERIOD?

In Step 3 - What am I actually creating here?

I appreciate the response but I feel it's missing a couple of steps, and dare I say it's a bit like Chat GPT wrote it 😬

[Anonymous]

Hi @ShaneHi 

You are right, I made a mistake here. Thank you for pointing it out. DATESINPERIOD only supports days, months, quarters, and years. Here is the changed DAX:

"Last 10 Weeks", DATESINPERIOD('Calendar'[W/C Monday], MAX('Calendar'[W/C Monday]), -10*7,Day)

Since I don't know your specific data structure and don't have any sample data, I can only provide you with a potentially effective method.

If you can provide sample data that does not contain any private information, it will greatly help in solving your problem.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ShaneHi]

Hi,

I'm still stuck with this and wondering if you can help some more.

As I asked in my last response, in step 2 - was I right to create a DAX Measure with this code?

And in Step 3 - I'm assuming "YOUR MEASURE" refers to what i'm trying to output? The problem is - i'm hoping to have the slicer as a Date Column in a Matrix which contains multiple measures

[ShaneHi]

Thanks for the response. I appreciate this is difficult without a desktop file to sample.

1) I created a table with this code

2) I created a measure with this code

3) I don't know how to create a measure with this code. What is "Your Measure Here" referring to?

I just keep hitting a wall with this code. My plan is to have a matrix table show the last 7 days of data (7 columns of previous days), 10 weeks of data (10 columns of the previous weeks) or 12 months of data (12 columns of previous months) depending what get's selected