Dynamic Date Filter Toggles

[Saffy]

i am very new to PBI, coming from a tableau background!

In tableau i could use a parameter field that would have two options: include and exclude

id then create an if condition, if inlcude then one date range, and if exclude then a different date range. I'd be able to add this a sheet filter.

I seem to be hitting a brick wall in delivering this in PBI. any guides/videos/tutorials or work around would be great

info about the data set

Key columns are [order_id], [order_date], [order_date_week]<truncated to monday, [order_date_week_num]

it seems to be failing because i want it as a column but this cant connect to a slicer to create that toggle function... any creative suggestions welcome 🙂

[v-sdhruv]

Hi @Saffy ,

Thank you for the update. Glad that it worked it for you.
If you face any challenges , feel free to reach out here. We would be happy to assist you.
Thank you for being a part of Microsoft Community Forum.

Regards,
Shruti

[v-sdhruv]

Hi @Saffy ,
Just wanted to check if the above suggestion has addressed your query.
If you are still facing any issues you can reach out with sample data (excluding sensitive data) and share excatly where you are having difficulty so that we can assit you better.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Thank You

[Saffy · Accepted solution]

i ended up creating the date/calendar table

then creating this calculated column Is_L5W_Exc & Is_L5W_Inc as below

Is_L5W_Exc =

VAR TodayDate = TODAY()

VAR StartOfCurrentWeek = TodayDate - WEEKDAY(TodayDate, 2) + 1

VAR StartOfPeriod6 = StartOfCurrentWeek - 35

RETURN

IF(

    Calendar[Date] >= StartOfPeriod6 &&

    Calendar[Date] < StartOfCurrentWeek,

    TRUE(),

    FALSE()

)

then in my measures i did

*Customer Count Last 5 Weeks =

VAR Mode = SELECTEDVALUE('Selector - Timeframe'[Options])

RETURN

SWITCH(

    Mode,

    "Inc", CALCULATE(DISTINCTCOUNT('FACT_CALLS'[UniqueIdentifier]), Calendar[Is_L5W_Inc] = TRUE()),

    "Exc", CALCULATE(DISTINCTCOUNT('FACT_CALLS'[UniqueIdentifier]), Calendar[Is_L5W_Exc] = TRUE()),

    BLANK()

)

[v-sdhruv]

Hi @Saffy ,

Thanks for the update.
It should ideally show the date range when selection is "Include". In the filter, you can select not blank and check if the value is showing correct number.

Thank You.

[v-sdhruv]

Hi @Saffy ,

Just wanted to check if you got a chance to review the suggestion provided and whether that helped you address your query?

Thank You

[Saffy]

ok my table is called sales

IsInDateRange =

VAR Selection = SELECTEDVALUE('Include/Exclude'[Parameter], "Include")

RETURN

CALCULATE(

    COUNTROWS('Sales'),

    FILTER(

        'Sales',

        IF(

            Selection = "Include",

            'Sales'[order_date] >= TODAY()-3,

             'Sales'[order_date] < TODAY()-3

        )

    )

)

so i've updated the formula and created it as a measure, but when i drag it onto a visual what do i filter these are the options

[v-sdhruv]

Hi @Saffy ,

In each visual (e.g., net sales, margin, etc.), add a filter: IsInDateRange= True

This way, the visual only shows data based on the selected toggle.

You don’t need to recreate this for every metric. Just use this IsInDateRange measure as a filter across visuals. Your actual metrics (like Net Sales, Margin, etc.) stay untouched.

Regards,
Shruti

[Shahid12523]

- Create a disconnected table with values like "Include" and "Exclude".
- Use a slicer on that table to let users choose.
- Write a DAX measure that checks the slicer selection and applies different date logic.
- Filter visuals using that measure (e.g., [IsInDateRange] = TRUE).

[Saffy]

It wouldn't let me create a measure from a column

[MasonMA]

Hi, 

After you created a disconnected table with below logic, 

Create a Measure, for example with below logic to show Past 3 days 'Request count' when user selects 'Include'. 

IsInDateRange = 
VAR Selection = SELECTEDVALUE('ParameterTable'[Parameter], "Include")
RETURN
CALCULATE(
    COUNTROWS('Request'),
    FILTER(
        'Request',
        IF(
            Selection = "Include",
            'Request'[Created Date] >= TODAY()-3,
             'Request'[Created Date] < TODAY()-3
        )
    )
)

[Saffy]

Do I have to do that for each calculation , like for each metric? Each measure or can that be applied to the filter
I have a few, net sales, margin, count orders, count of products etc..

av