Dividing something by the working days last month

[mmace1]

Hi,

I have a Formula  that calculates how many notes were created last month:

Last Month Pmt Notes = CALCULATE(count('Payment Notes'[User]),'Payment Notes'[Is note previous month?]=TRUE)

The [Is note previous month?] comes from calculated columns.  One that calculates 1 month ago, one that calculates the month to the date of the note, and a third that compares them and sees if they're equal. 

I'd like to also divide the result of the above by the number of working days in the previous month.  So [Last Month Pmt Notes]/<?>

Dividing by the total days last month seems easy using eomonth, but how to divide just by the weekdays? 

[v-ljerr-msft · Accepted solution]

Hi @mmace1,

If I understand you correctly, you may need to create another calculate column to indicate if a date is working day or not first. Then it will be easy to calculate the number of working days last month with a filter as [IsWorkingDay] = TRUE.

To create the [IsWorkingDay] calculate column, you can refer to this article which describes how to calculate working days in data model by using DAX. 

Regards

[mmace1]

OK, the way I ended up solving it for working days per month was:

Added a column to my calender table that returned "1" for working day, and "0" for not. 

Then to get the number of working days last month:

calculate(sum('Calender Table'[Is Weekday?]),'Calender Table'[Date]<=eomonth(today(),-1),'Calender Table'[Date]>eomonth(today(),-2))

Edit:  For Holidays, created table in Excel with 2 columsn.  First is all the 2017 dates, second is a 0 or 1 (1 = one of our holidsays).  Uploaded to Power BI, then connected to my main calendar table, and pulled over the 0s/1s.  Then added a third value to the CALCULATE formula above, that checkes if the holiday value is 0.