Working Days Calc - Need Help

[jl20]

Hi all,

I'm having a lot of trouble writing the syntax for a seemingly simple requirement. 

Table 1: [employee name] [last worked date]

Table 2: Standard date table, ending with today's date

Desired Output:

Employee name                Date Last Worked                Potential Working Days*        Total Potential Working Day in Current Month**

John Smith                           2/15/18                                    11                                          17

Jane Doe                              2/2/18                                       2                                           17

etc.

*Equal to count of weekdays in the current month, subject to the limitation that they must have occurred before the Date Last Worked.

** Will be the same for all employees - in this case, there are 17 working days between Feb 1 and Feb 23 (including today).

I'm using the following expression, where IsWorkingDay is a calc column on the date table with 1s (Weekdays)/0s (Weekends), and it's working: 

Potential Working Days in Current Month =

CALCULATE(

 SUM('Calendar'[IsWorkingDay]),

   DATESBETWEEN('Calendar'[Date],EOMONTH(TODAY(),-1),TODAY())          -1                                           )

I've found several proposed solutions on the forum but am not having any luck and my memory is getting eaten up and failing using the SUMX solution.

Any insight would be appreciated.

Thanks!

[Zubair_Muhammad]

@jl20

Try this calculated column

Assuming workingdays from Monay to Friday

Potential Working Days =
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES (
            DATE ( YEAR ( Table1[Date Last Worked] ), MONTH ( Table1[Date Last Worked] ), 1 ),
            Table1[Date Last Worked]
        ),
        "Week_Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    COUNTROWS ( FILTER ( mytable, [Week_Day] <= 5 ) )

[Zubair_Muhammad]

@jl20

[jl20]

Thanks! That worked for that piece of the equation. Is there a clean way to calculate potential working days for the entire month, if my date table only goes through today?

For example, because of that limitation, my count is only 18 using this formula: 

Working Days in Month = CALCULATE(SUM('Calendar'[IsWorkingDay]),DATESBETWEEN('Calendar'[Date],EOMONTH(TODAY(),-1),EOMONTH(TODAY(),0)))

Thanks

[TomMartens · Accepted solution]

Hey, you can use this DAX statement to calculate the total available workingdays

Total Working Days = 
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES (
            DATE ( YEAR ( employee[LastWorkingDate] ), MONTH ( 'employee'[LastWorkingDate] ), 1 ),
            DATE ( YEAR ( employee[LastWorkingDate] ), MONTH ( 'employee'[LastWorkingDate] )+1, 1) -1
        ),
        "Week_Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    COUNTROWS ( FILTER ( mytable, [Week_Day] <= 5 ) )

Here is a little screenshot

[jl20]

That works, thanks!

[Zubair_Muhammad]

@jl20

For the full month's working days, you can replace

TODAY()

with
EOMONTH ( Table1[Date Last Worked], 0 )

[TomMartens]

@Zubair_Muhammad an elegant usage of GENERATESERIES(...)

[Zubair_Muhammad]

@jl20

Similarly

Total Potential Working Days in Current Month =
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES (
            DATE ( YEAR ( Table1[Date Last Worked] ), MONTH ( Table1[Date Last Worked] ), 1 ),
            TODAY ()
        ),
        "Week_Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    COUNTROWS ( FILTER ( mytable, [Week_Day] <= 5 ) )