Counting workdays...

[Anonymous]

I have the master calendar all set with holidays flagged.  I created WorkDay =1 or 0 to indicate if its a workday  or not (sat, sun and holidays = 0). 

CALCULATE(SUM(mastercalendar[WorkDay]),DATESBETWEEN(mastercalendar[full_date],requests[date1],requests[date2]))  returns 1, 0 or blank.

I want to sum up the workdays between date1 and date2.

Not sure what's going wrong.

[Anonymous]

So we found the issue.  Apparently relationships are active in a calculation so it first filtered the calendar for one date and took the IsWorkday which is only 0 or 1.  So now the question becomes - how can I suppress the relationship for this column ?

1. Find a way to do this in power query

2. Find a way to do this in DAX.

Scouring the community for a solution

[Anonymous · Accepted solution]

Crossing my fingers.  Seems to work (the missing piece of the equation)

WorkdaysBetween =

CALCULATE(

SUM(mastercalendar[IsWorkday]),

ALL(mastercalendar),

DATESBETWEEN(

mastercalendar[full_date],

requests[requestdate],

requests[completedate]

)

)

In case you're interested, in power query

Table.AddColumn(#"DayOfWeek", each Date.DayOfWeek([full_date]))

Table.AddColumn(#"IsWeekday", each if [DayOfWeek]=0 or [DayOfWeek]=6 then 0 else 1)

Table.AddColumn(#"IsWeekend", each 1- [IsWeekday])

Table.AddColumn(#"IsHoliday", each if [Holiday]="" then 0 else 1)

##holidays were built using Date patterns

Table.AddColumn(#"IsWorkday", each if [IsWeekend]+[IsHoliday]>0 then 0 else 1)

[Ashish_Mathur]

Hi,

I do not see a mistake there.  Share the link from where i can download your PBI file.

[amitchandak]

I created to columns in One of my Calender

WeekDay = WEEKDAY('Compare Date'[Compare Date])
WeekDay = WEEKDAY('Compare Date'[Compare Date])

And I able to sum same within this calendar

Also There is another Calendar not join to this one, Able to do with that to

Weekdays Second Cal = ( VAR _Cuur_start = Min('Date'[Date Filer]) VAR _Curr_END = Max('Date'[Date Filer]) return calculate(sum('Compare Date'[Working Day]),'Compare Date'[Compare Date] >= _Cuur_start && 'Compare Date'[Compare Date] <= _Curr_END ) )