Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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. 
 
Need Help

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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