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)
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

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