Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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 ) )
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
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
That works, thanks!
For the full month's working days, you can replace
TODAY()
with
EOMONTH ( Table1[Date Last Worked], 0 )
@Zubair_Muhammad an elegant usage of GENERATESERIES(...)
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 ) )