Forum Discussion
Count workdays with between to dates unless training date is after start date
Hi, Anonymous
Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous3 years agoNot applicable
My problem I want to solve is when the training date is later than the start date in the slicer, I want it updated to show the start date as the training date and recalculate the number of required activities.
I have a DimDate table where I have a column stating true or false if the date is a workday, (it excludes weekends and Holiday)
Each person record has a column with the number of activities per day required based on job title. Which is 1 per workday for supervisors and 3 per week for managers.
I have at training file with the date they were trained in the process.
This is the current calculating the total activities due for 10/1/22-10/31/22 our company has no holidays in this month.
The first supervisor was trained on 10/17/22
The MGR II was trained on 10/20/22
The other two were trained last year.
The table should show:
The first supervisor as required to 10 in the Goal column
The MGR II with a goal of 4 days
The other two supervisors are correct.
This is the formula for counting only work days.
NBR Days = --this excludes holidays and weekend--
COUNTROWS(
FILTER(DimDate,DimDate[SA_Workday] = TRUE()))
This is the formula for assigning the goal.
Min SC Expectation = // CrossFilter ignores the relationship between dim date table and safety events table to get all users and goals listed
calculate(SUMx('All Employees','All Employees'[Min Contact/Week]) * [NBR Days], CROSSFILTER(DimDate[Cal_Date_DT],'Safety Event Entry_Safety Event Entry - Daily Report'[Date Performed], None))