cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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
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 =
    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

 

2018-02-23_22-26-22.png



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

View solution in original post

8 REPLIES 8
Highlighted
Super User III
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 =
    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 ) )
Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

Re: Working Days Calc - Need Help

@jl20

 

workingdays.png

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

Re: Working Days Calc - Need Help

@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 ) )
Try my new Power BI game Cross the River
Highlighted
Super User IV
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
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
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
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 =
    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

 

2018-02-23_22-26-22.png



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

View solution in original post

Highlighted
Helper III
Helper III

Re: Working Days Calc - Need Help

That works, thanks!

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors