Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Counting workdays...

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]),DAT...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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)