Forum Discussion
Dynamically Calculate a Measure based on Date Slicer Selection
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
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?
