Forum Discussion

Saffy's avatar
Saffy
New Member
10 months ago
Solved

Dynamic Date Filter Toggles

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...
Need Help
Tips and Tricks
  • Saffy's avatar
    Saffy
    7 months ago

    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's avatar
v-sdhruv
Community Support
9 months ago

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's avatar
Saffy
New Member
9 months ago

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