Date Filters within a Date Parameter

[ShaneHi]

Hi,

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 built out a slicer using this guide, to build a Date Parameter table that pulls specific columns from my Calendar Table. I then dropped this said code into the column field of a Matrix table, and it works perfectly (e.g. When I select D, it shows my data in the matrix in Days, When I select W, it shows my data in the matrix as Weeks etc).

I'd just like to be able to have a bit of a filter on the date ranges, but I can't figure out how!

The code i'm using within my Parameter is:

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

Is it even possible to filter the date range here?

Thanks

[Anonymous]

Hi @ShaneHi 

Please refer to the following post:
Power BI - Custom Date Range Filter Support - Microsoft Fabric Community

Best Regards,

Jayleny

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

[_AAndrade]

Hi @ShaneHi,

Please take a look at this post: https://community.fabric.microsoft.com/t5/Desktop/Last-month-last-week-last-n-days-buttons-filters/m...

See if my solution could help you.

[johnt75]

You could create a table similar to the below

Date Range Table =
SELECTCOLUMNS (
    UNION (
        GENERATE (
            { "Last 7 Days" },
            DATESBETWEEN ( 'Date'[Date], TODAY () - 7, TODAY () )
        ),
        GENERATE (
            { "Last 10 Weeks" },
            DATESBETWEEN ( 'Date'[Date], TODAY () - 70, TODAY () )
        ),
        GENERATE (
            { "Last 12 Months" },
            DATESBETWEEN ( 'Date'[Date], EOMONTH ( TODAY (), -13 ) + 1, TODAY () )
        )
    ),
    "Date Range", [Value],
    'Date',
    'Date'[Date]
)

You may want to tweak the start and end dates if you want full weeks, or if you want the latest date to be yesterday.

Create a single direction many-to-many relationship with the Date table so that the 'Date Range Table' filters the date table and not the other way around.

You can now use the date range column in visuals and it will filter the date table accordingly.

This wouldn't work for a custom date range, but you could probably add an entry of { "Custom", BLANK() } into the union and then set up a normal slicer on the date table to achieve that.

[SamWiseOwl]

Hi @ShaneHi 

When you create a date slicer does it not filter your list of dates?

[SamWiseOwl]

@ShaneHi 

Assuming you don't want to filter the data but rather remove the rows you could create a new column.

Create a new column in your calendar table something like this:
FDate =
If( [Date] >= date(year(today()), 01,01), [Date], blank())

Then add a row to your slicer table

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