Create measure based on slicer date

[hpatel247]

Hi,

Not sure if it is possible but i want to display data based on a date selected by end user which can change. My data consists of service users with different services which are open and closed. So for example if i had a service user with home care with the start date of 10/04/2014 and end date of 26/07/2017, if the end user selects 20/06/2017 - this service user will be displayed but if they select 12/08/2017, this service user will not be displayed.

Service User Name            Service Type      Start Date            End Date

J Bloggs                              Home Care       01/04/2017       14/04/2017

J Bloggs                              Day Care          28/06/2017        29/06/2017

J Bloggs                             Residential Care   01/04/2015      20/08/2017

Another example as above, based on the date selected above i would only expect to see Day Care and Residential care for the first date and then just residential care for the second date

Is this possbile in any way?

regards

Hetal

[v-yuta-msft]

Hi hpatel247,

Based on your description, you have create a calendar table and set a slicer on the date column of calendar table, then use the slicer to filter another table, right?

You can use DAX formula like this:

result = CALCULATE(MAX(Table1[Service Type]), FILTER(Table1, Table1[Start Date] <= SELECTEDVALUE('calendar table'[Date]) && Table1[End Date] >= SELECTEDVALUE('calendar table'[Date])))

Result is like below:

 

PBIX here: https://www.dropbox.com/s/xensfubqmjp4fu9/Create%20measure%20based%20on%20slicer%20date.pbix?dl=0

Regards,

Jimmy Tao

[hpatel247]

Hi Jimmy,

Thank you for your response. I have tested this but still doesn't show what i need it to show. Based on your formula, if the date was set as 20/08/2017, the result will be displayed however if you select 19/08/2017, i would still expect the result to be displayed but it doesn't.

I am working with start and end dates and whichever date is selected, it needs to show all services that were still open as at that date if this makes sense.

Very grateful for your assistance with this.

kind regards

Hetal