Help With Networkdays Excluding Holidays Using Calendar Table

[rbowen]

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 

[lbendlin]

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]

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.

[Ashish_Mathur · Accepted solution]

Hi,

Please check the attached file.

[rbowen]

Thank you Ashish, that appears to have done the trick. And thank you @lbendlin , your answer was part of the solution as well, I had a filter set incorrectly that causing some of the data to go missing. Many thanks to you both. 

[Ashish_Mathur]

You are welcome.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...