Power Pivot Daily Avg - Business Days Per Month

[cmeredith73]

Hi - I currently use Power Pivot to calcuate a daily average of salesforce case activity. In that KPI, I can create [Total Volume]/[Distinct Day Count in Month].

The [Distinct Day Count in Month] is a Measure based upon DistinctCount([Day In Month]) - but if I try to force that to be over '20 business days' the formula won't work correctly.

Ever come across a solution to solve?

 

[Chihiro]

There are number of ways to deal with it.
But easiest is to include in your Calendar dimension table, that calculates IsWorkingday (Boolean).
Based on list of holidays and Date.DayOfWeek().

Then it's simple matter of counting rows where IsWorkingDay = True.

 

[cmeredith73]

Hi - I have to figure out creating the dimension table in excel, then will work back to your suggestion. I can understand there is a few moving parts to this.

There are number of ways to deal with it.
But easiest is to include in your Calendar dimension table, that calculates IsWorkingday (Boolean).
Based on list of holidays and Date.DayOfWeek().

Then it's simple matter of counting rows where IsWorkingDay = True.

 

[cmeredith73]

Hey progress and thank you for the point in the right direction. I now have my calender dimension table and it calculates if Sat/Sun are workdays, true/false based on the weekdate). Now I just have to set the formula in my power pivot to look up count the number of T vs F in the given month.