• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Pivot Daily Avg - Business Days Per Month

cmeredith73

New Member
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

Excel Ninja
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

New Member
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

New Member
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.
 
Top