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
RichFlorida's avatar
RichFlorida
Icon for Helper V rankHelper V
2 years ago

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!

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