topic Re: Filtering on a date range based on slicer value in DAX Commands and Tips

Filtering on a date range based on slicer value

RichFlorida — Tue, 25 Jun 2024 21:25:51 GMT

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 the endDate of the week, and number of events (see code below).

Num of Events = var startDate = SELECTEDVALUE(SearchTable[COMPLAINT_DATE]) var endDate = startDate + 6 return CALCULATE( COUNTA( SearchTable[EVENT_NUMBER]) , (SearchTable[COMPLAINT_DATE] >= startDate && SearchTable[COMPLAINT_DATE] <= endDate) )

I'm having an issue with the totals displayed as they are only for the startDate day that is selected at the slicer (only for one day) and not the whole week.

Could someone please advise what I need to do to have the measure display the total for a whole week not just the one day.

Thanks!

Re: Filtering on a date range based on slicer value

vicky_ — Tue, 25 Jun 2024 22:44:50 GMT

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])

Re: Filtering on a date range based on slicer value

RichFlorida — Wed, 26 Jun 2024 12:30:15 GMT

Hi vicky_

I want the user to select the date the week starts from the slicer, I want my measure to calculate totals for the whole week.

Currently it is only calculating the total for the specific date selected at the slicer and not for the date range in the measure.

Any help greatly apreciated.

Re: Filtering on a date range based on slicer value

RichFlorida — Wed, 26 Jun 2024 22:37:35 GMT

Could someone please provide some insight...

I trying to find out why my filter is not working for the date range specified in the measure "Num of Events", and instead results are only displayed for the startDate.

Thanks!

Re: Filtering on a date range based on slicer value

tamerj1 — Thu, 27 Jun 2024 03:24:55 GMT

Hi RichFlorida
Get a Date table with minimum Date and Start of Week columns. Create the relationship between 'Date'[DATE] and 'SearchTable'[COMPLAINT_DATE] and place 'Date'[Start of Week] in the slicer. Then a simple count aggregation will do.

Re: Filtering on a date range based on slicer value

RichFlorida — Thu, 27 Jun 2024 12:49:08 GMT

Hi tamerj1 ,

Thank you for your post.

I already have a date table, the date on the slicer are the "start of week" dates.

I capture the selected "Start of week" date and use the measure below to get the count for a whole week.

However it is not working as the slicer value overrides my measure and force it to display total for the selected date/day only.

Pls advise!