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
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
2 years ago

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.

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

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!

 

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