Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using Date Slicer as Variable in a Measure

Hi Power BI Community,

 

I've ran into an issue when trying to use a date slicer as a variable in a measure. The case is as follows:

 

  • I have a list of people who have two dates associated with them, DateOnHold and DateOffHold.
  • The user will control a date slicer to select a calendar month.
  • The measure should calculate the number of people who were on hold going into the selected month and ignore people who came off hold before the selected month.

 

A sample of the data table looks like:

 

IDOnHoldOffHold
101/03/201701/04/2017
212/08/201912/08/2020
306/04/202006/08/2020
421/05/2020 
508/12/201828/12/2018
616/09/201901/01/2020
731/07/2020 
815/03/201904/05/2019
927/11/201805/12/2018


In this instance, I would like to use a date slicer to select 01/08/2020 - 31/08/2020 and it return a count of 4 as two records have no defined OffHold date and the other two's end date is later than the first date of the month selected. Furthermore, if there is a handy way to summarise this into a table to see the people currently on  hold as well based on the user inputted slicer, that would be amazing.

My attempt hasn't worked well:

IDOnHoldOffHold
101/03/201701/04/2017
212/08/201912/08/2020
306/04/202006/08/2020
421/05/2020 
508/12/201828/12/2018
616/09/201901/01/2020
731/07/2020 
815/03/201904/05/2019
927/11/201805/12/2018
OnHold =
VAR minDate = MIN(Carer_On_Hold[Date_On_Hold])
VAR maxDate = MAX(Carer_On_Hold[Date_On_Hold])
RETURN

CALCULATE(
            COUNT(Carer_On_Hold[CarerID]),
FILTER(Carer_On_Hold,[Date_On_Hold] > minDate), FILTER( Carer_On_Hold,[Date_Off_Hold] < maxDate))

Cheers,

cmob
 
Need Help

3 Replies