How to have slicers change the result of measures?

[boujarwah]

I have a data set with a list of employees and the start and end date of their contracts. I created a column that calculates the days worked for each of those contracts.

However, I want to implement a date slicer on the report to allow users to see how many days employees worked between two specified dates.

Currently, if I add a slicer with the date table i set up, i am able to filter down to the employees that worked between those dates, but the Days Worked calculation is still showing the overall value, not the one specific to the slicer. How do link the measure/calculation to the slicer?

[Anonymous · Accepted solution]

Hi @boujarwah ,

Here's my solution.

1.There's no relationship between the calendar table and the main table.

2.Create the workdays measure.

Workdays = COUNTROWS(FILTER('Calendar',[Date]<MAX('Employee'[Employee End Date])&&[Date]>MAX('Employee'[Employee Start Date])))

You can check more details from my attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[davehus]

Hi @, Have you got some sanitized data of your problem you can share?

[boujarwah]

Hi @davehus . Yes, its very basic, one column is the start date, the other is the end date. The Days Worked is currently all at 94 because I have it calculating based on today, not the entire contract. So the next step, is for the days worked to be calculated based on the date slicer to the left. 

[davehus]

Hi @, is the days worked a calculated column or measure?

[boujarwah]

It is currently just a new column with a nested If function, but done in the data tab not in Power Query Editor. 

[davehus]

Hi @, A calculated column is calculated in memory so doesn't respond to a filter. You would need to create it as a measure. I don't understand the layout of your model, so can't suggest a measure for you. 

[boujarwah]

Sorry, what is not clear about the model? I have a table with all employees and their start and end date of all their contracts. Ive created a separate date table as well. The slicer is curently linked to the date table, so would i need to reference that in the measure i create? 

The main goal here is to have a column that calculates the days worked by the employee, between two dates set by the slicer. 

For Example: Slicer set to 1/1/2022 - 2/1/2022

Employee Start Date: 1/15/2022 

Employee End Date: 3/1/2022

DESIRED RESULT: Days worked: 17 days

CURRENT RESULT: Days Worked: 45 days

[JohnLow]

You have a date table setup, do you have a column of whether the day is a work day or not?

Your measure should then look something like this.

Workdays w/o weekends =
CALCULATE (
    COUNTROWS ( 'dimCALENDAR' ),
    DATESBETWEEN (
        'dimCalendar'[Date],
        'fctemployeedetails'[Employee Start Date],
        'fctemployeedetails'[Employee End Date]
    ),
    'dimCalendar'[ISWORKDAY] = 1,
    ALLSELECTED ( 'fctemployeedetails' )
)

[boujarwah]

Thank you for the help, it is actually simpler than that even, as i can consider a 7 day work week.  Unfortunately, that still results in the days worked for the contract itself, and does not account for the slicer. 

For Example: Slicer set to 1/1/2022 - 2/1/2022

Employee Start Date: 1/15/2022 

Employee End Date: 3/1/2022

DESIRED RESULT: Days worked: 17 days

CURRENT RESULT: Days Worked: 45 days