NetWorkDays between Min Date and Max Date, ignoring dimension

[acbishop2]

Hi All,

I have a formula to grab the total numbers of business days in the date range selected as follows:

NetWorkDays(Min([SalesDate]),Max([SalesDate]),$(vHolidays))

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.

Employee	Sales	NetWorkDays(Min([SalesDate]),Max([SalesDate]),$(vHolidays))	Sales/Work Day
Mickey Mouse	5	2	2.5
Donald Duck	3	3	1
Minnie Mouse	8	2	4

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.

[Taoufiq_Zarra]

@acbishop2  can you elaborate o share a sample data and the expected output ? thanks

[acbishop2]

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:

Employee	Sales	NetWorkDays(Min([SalesDate]),Max([SalesDate]),$(vHolidays))	Sales/Work Day
Mickey Mouse	5	5	1
Donald Duck	3	5	0.6
Minnie Mouse	8	5	1.6

[Almen · Accepted solution]

Try the following:

NetWorkDays(Min(TOTAL [SalesDate]),Max(TOTAL [SalesDate]),$(vHolidays))

[acbishop2]

That did it! Thanks so much!

[shanemichelon]

If you have Mickey Mouse, Donald Duck and Minnie Mouse all working together, you can safely just hard-code the column to zero.