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

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 ) )