Forum Discussion

RichFlorida's avatar
RichFlorida
Icon for Helper V rankHelper V
2 years ago

Filtering on a date range based on slicer value

Hello All, In my report, i'm using a Matrix Visual to display the total of weekly events.      I have a slicer for users to select the startDate of the week. I use a measure to calculate th...
Need Help
vicky_'s avatar
vicky_
Icon for Super User rankSuper User
2 years ago

I would update my calendar table to have more than 1 day included in the "week of" part:

The below would give me my base calendar table:

Calendar Table = ADDCOLUMNS(CALENDAR(DATE(2020, 1, 1), DATE(YEAR(TODAY()), 12, 31)),     
    "WeekNum",  INT(
        SWITCH(TRUE(),
            MONTH([Date]) = 1 && WEEKNUM ([Date] + 1, 21) > 50, YEAR([Date]) - 1, 
            MONTH([Date]) = 12 && WEEKNUM ([Date] + 1, 21) = 1, YEAR([Date]) + 1,
            YEAR([Date])
        ) & FORMAT(WEEKNUM ([Date]  + 1, 21), "00"))
)

The weeknum is taken from another community thread: https://community.fabric.microsoft.com/t5/Desktop/How-to-get-the-ISO-year-in-DAX-while-ISO-week-looks-so-easy-this/td-p/1870758, and it's there to make sure that i have 7 days per week (even if it crosses over a year).

On the above table, I create a calculated column:

Week Of = FORMAT(CALCULATE(MIN('Date'[Date]), ALLEXCEPT('Date', 'Date'[WeekNum])), "dd/mm/yy")

And then to update your measure, you can remove the calculations for the week range:

Num of Events = COUNTA( SearchTable[EVENT_NUMBER])