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

4 Replies

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

     

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 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
      7 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)