Forum Discussion
Dynamically Calculate a Measure based on Date Slicer Selection
I'm currently experiencing an issue I need help resolving.
I have two datasets representing a workforce, one with an active roster with start dates, and one with all of the people who've left and their separation dates. I want to create a measure that shows the total number of people on the roster when the date is filtered. So the measure would need to subtract anyone past their separation date but count everyone past their start date. I have a date table created from the minimum and maximum dates, so I was thinking to make a calculated column in the date table that has the total number for that date, then use the date table date column for filtering. But even though I defined a relationship between the date table's date column and the start date column of the roster, it won't let me use RELATED() to compare them for some reason. Is there any way I can achieve this use case?
For context, I was trying to do a measure like this:
Thank you!
Hi tssinha ,
Create two measuresTotal = VAR ResignationDate = IF( SELECTEDVALUE(Resignations[Resignation Date]) = BLANK(), DATE(2024,12,31), SELECTEDVALUE(Resignations[Resignation Date]) ) RETURN CALCULATE( COUNT('Active Roster'[Name]), FILTER( 'Active Roster', SELECTEDVALUE('Active Roster'[Start Date]) <= MIN('Date'[Date]) && ResignationDate >= MAX('Date'[Date]) ) )Result = CALCULATE( COUNTROWS('Active Roster'), FILTER( 'Active Roster', [Total] = 1 ) )Ensure there is a relationship between two table
Final output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
- dharmendars007
Memorable Member
Sure, here's some fake sample data to illustrate the point:
Active Roster:
Name Start Date Name1 6/1/2024 Name2 6/2/2024 Name3 6/5/2024 Name4 6/10/2024 Name5 6/13/2024 Resignations
Name Resignation Date Name1 6/7/2024 Name3 6/25/2024 If these were the two tables, then I'd want the measure to show:
3, when the date is filtered anytime before 6/7/2024
2, when the date is somewhere between 6/7/2024 and 6/10/2024
3, when the date is between 6/10/2024 and 6/13/2024
4, when the date is between 6/13/2024 and 6/25/2024
3, when the date is after 6/25/2024
the current date I'm using for the slicer is start date but I have a date table so I can use that instead if the solution relies on it
Hope this helps!
Hi tssinha ,
Create two measuresTotal = VAR ResignationDate = IF( SELECTEDVALUE(Resignations[Resignation Date]) = BLANK(), DATE(2024,12,31), SELECTEDVALUE(Resignations[Resignation Date]) ) RETURN CALCULATE( COUNT('Active Roster'[Name]), FILTER( 'Active Roster', SELECTEDVALUE('Active Roster'[Start Date]) <= MIN('Date'[Date]) && ResignationDate >= MAX('Date'[Date]) ) )Result = CALCULATE( COUNTROWS('Active Roster'), FILTER( 'Active Roster', [Total] = 1 ) )Ensure there is a relationship between two table
Final output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- Ashish_Mathur
Super User
Hi,
Why should the answer be 3,4 for "when the date is between 6/10/2024 and 6/13/2024" and "when the date is between 6/13/2024 and 6/25/2024" respectively? Who are those employees?
