Forum Discussion

rbowen's avatar
rbowen
Helper III
1 year ago
Solved

Help With Networkdays Excluding Holidays Using Calendar Table

I need to calculate the number of budget units per day from a table that only gives the budget numbers in terms of the entire month. I've created a calendar table that dynamically calculates when US holidays fall on a weekday and it has a one to many relationship with my budget table. One of the columns in the calendar table is Holidays, which lists the name of the holidays that fall on weekdays. I've attached a link to a sample pbix file for reference. A calculated column in my budget table uses the NETWORKDAYS function to calculate the number of workdays between each period's start and end dates. I also have a calcuated column that determines that amount of budget units per days based on total workdays and entire month budget. In the screenshot below, the WorkDays column shows 23 days for January, but the number should be 22 taking January 1st into account. 

 

 

I've tried various ways to get the NETWORKDAYS function to use my calendar table but am having no luck. Ultimately, the DAX needs to calculate the number of weekdays between the PeriodStartDate and PeriodEndDate columns in the BudgetUnits table based on when the Holidays column in the Calendar table is blank. Is this possible?

 

Thank you. 

 

Sample PBIX File 

Need Help

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    NETWORKDAYS expects a separate table for the holidays.  You have the holidays included as a column in the calendar table, so you don't need to use NETWORKDAYS.

     

    Your BudgetUnits[Workdays] is a calculated column and will ignore the date slicer. Same with the BudgPerDay.

     

     

     

     

     

     

    • rbowen's avatar
      rbowen
      Helper III
      1 year ago

      lbendlin - 

       

      Thank you, that got the number working days correct. I'm finding that my calculation for determining the month to date value for the budget units isn't working, specifically with a YearMonth slicer instead of a date slider. When placed into a table, the value is blank. Both BudgetUnits[Workdays] and BudgPerDay functions are now measures instead of calculated columns.  I need to display the MTD value of the budget units based on what YearMonth value users select in the YearMonth slicer.