Count workdays with between to dates unless training date is after start date

[Anonymous]

I have a formula to count the workdays it works well. I use the date slicer for the date and it returns the number of workdays selected. 

current formula

COUNTROWS(

     FILTER(DimDate,DimDate[SA_Workday] = TRUE()))

However this is used to determine the quanity of activities a user has completed, if they are newly trained I would like it to adjust by their training date being as far back that can be counted.  

That way if their training date is greater than the slicer start date the calulation adjusts to start counting days on their training date. 

[v-zhangti]

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.

[Anonymous]

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))