I have a formula to grab the total numbers of business days in the date range selected as follows:
This is close to what I want, but I need it to ignore dimensions. For example, I want to be able to calculate an employee's sales per work day. In the table below, my formula gives me only the number of days in which an employee made a sale, not the total number work days in the selection.
I hope that makes sense. Essentially, I want to do something similar to COUNT( DISTINCT TOTAL SalesDate ) but with work days rather than calendar days.
Thanks in advance for the help.
@acbishop2 can you elaborate o share a sample data and the expected output ? thanks
So the problem is shown in the third column of the table in my previous post.
For example, if I filter the SalesDate by Year = 2020, Week = 38, that will give me the date range of 9/13 - 9/19. In that date range, there are 5 business days. So if there are no dimensions applied, my formula NetWorkDays(Min([SalesDate]),Max([SalesDate]),$(vHolidays)) will return the value 5. This is the value I want, and it represents the total number of workdays in the selected date range.
However, when I throw this formula into a table with the dimension of Employee, it starts returning the total number of workdays between the first day the employee made a sale and the last day the employee made a sale in the selected date range.
So, in the above example, lets say in week 38, Mickey Mouse makes 5 sales, but they were all on Monday and Tuesday. This makes it so that the formula for work days selected returns the value 2 (but I want it to be 5 still).
The desired output for this scenario is: