Include slicer date value in a measure

[Anonymous]

Hi everyone,

I have a dimension table dim_hr_employment_details where there is the hire date and the termination date of each employee, and a DimDate (calendar) table. In my model there is an active relationship between both tables using the termination date of dim_hr_employment_details and the date of DimDate, and an inactive relationship using respectively the hire date and the date.

In my report, I have a slicer that simply uses the date from DimDate.

And when selecting e.g. 2022 in the slicer, I would like to compute a measure that counts the number of employees for which the slicer date value (in this case ; 01/01/2022) is between their hire date and termination date.

Could someone help me out with that?

Thanks a lot!

[Anonymous]

Hi @FreemanZ ,

so I would like to create a measure that includes the date that I select in my date slicer.

If I create a simple measure that counts the number of employees and I put it in a visual, when I select for example 2022 in my date slicer I will only have the number of employees whose termination date (or hire date if I use the inactive relationship) is 2022. But what I would like to have is a measure that counts the number of employees for which the value in my date slicer (e.g. 2022) is bigger than their hire date AND smaller than their termination date.

Thanks!

[FreemanZ · Accepted solution]

hi @Anonymous 

Your logic is very clear. Not sure about your dataset. You may

1) create an isolate datetable

2) write a measure like this:

ActiveEmp = 
VAR _date = SELECTEDVALUE(DateTable[Date])
RETURN 
COUNTROWS(
    FILTER(
        TableName,
        TableName[HireDate]<=_date
            &&TableName[EndDate] >= _date
    )
)

3) plot a slicer with the date column from the datetable and another visual (e.g. Card) with the measure. 

I tried and it worked like this:

the sample dataset:

the datetable:

To use slicer value as filter boundary, the sliced table shall be isolated. Otherwise, the slicer filters a single value. 

[Anonymous]

Thanks a lot @FreemanZ, however as in my data model there is an active relationship between the end date and the date of the datetable, but an inactive one between the hire date and the date of the datetable, it returns blank when I create the measure you wrote. What should I do differently in the measure?

[FreemanZ]

hi @Anonymous 

you are welcome. Then create an new isolated date table for the slicer. 

[Anonymous]

@FreemanZ I've created another date table DimDate2 and here is now my formula :

VAR _date = SELECTEDVALUE(DimDate[Long Date])

VAR _date2 = SELECTEDVALUE(DimDate2[Long Date])

RETURN

COUNTROWS(

    FILTER(

        dim_hr_employment_details,

        dim_hr_employment_details[HireDate] <= _date2

        && dim_hr_employment_details[TerminationDate] >= _date

    )

)

But it returns blank. Is there something wrong in my measure?

[FreemanZ]

hi @Anonymous 

both DimDate and DimDate2 need to be isolated from the dim_hr_employment_details table. Is that so in your case?

[Anonymous]

Hi @FreemanZ, what do you mean exactly by isolated?

For the moment, my tables are like this...

[FreemanZ]

Hi @Anonymous 

I mean delete all the relationships for the tables that you put into a slicer to get the filter boundaries.

[FreemanZ]

hi @Anonymous 

could you further explain the logic of this part:

when selecting e.g. 2022 in the slicer, I would like to compute a measure that counts the number of employees for which the slicer date value (in this case ; 01/01/2022) is between their hire date and termination date.