Forum Discussion

jl20's avatar
jl20
Helper IV
8 years ago
Solved

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 d...
Need Help
  • TomMartens's avatar
    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