Forum Discussion

ShaneHi's avatar
ShaneHi
Frequent Visitor
1 year ago

Power BI - Custom Date Range Filter Support

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
ShaneHi
Frequent Visitor
1 year ago

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!