Working Days Calc - Need Help

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!

Need Help

TomMartens

8 years ago

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