cancel
Showing results for
Did you mean:
Highlighted
Helper III

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: Working Days Calc - Need Help

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

Total Working Days =
VAR mytable =
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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
8 REPLIES 8
Highlighted
Super User III

## Re: Working Days Calc - Need Help

@jl20

Try this calculated column

Assuming workingdays from Monay to Friday

Potential Working Days =
VAR mytable =
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 ) )
Try my new Power BI game Cross the River
Super User III

## Re: Working Days Calc - Need Help

@jl20

Try my new Power BI game Cross the River
Highlighted
Super User III

## Re: Working Days Calc - Need Help

@jl20

Similarly

Total Potential Working Days in Current Month =
VAR mytable =
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 ) )
Try my new Power BI game Cross the River
Highlighted
Super User IV

## Re: Working Days Calc - Need Help

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Helper III

## Re: Working Days Calc - Need Help

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

Highlighted
Super User III

## Re: Working Days Calc - Need Help

@jl20

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

TODAY()

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

Try my new Power BI game Cross the River
Highlighted
Super User IV

## Re: Working Days Calc - Need Help

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

Total Working Days =
VAR mytable =
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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Helper III

## Re: Working Days Calc - Need Help

That works, thanks!