Forum Discussion

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