Wolfster63
New Member
- Joined
- May 2, 2018
- Messages
- 24
Trying to determine the total available workdays in a Power Pivot table that adjusts to the date range that my table is looking at.
My pivot table examines how many minutes a particular operating room is used during a week, month, Day of the Week. I can calculate the number of minutes the room was used without difficulty, but am having trouble with returning the overall number of days it was available for use.
For Example If I'm looking at January 2022, it would return 21 days. Or If I'm looking at the second week in January, it would return 5 days, etc. My pivot table finds the number of days in the period that the room was occupied using the DISTINCTCOUNT function which is the numerator. But I can't seem to find the denominator which would be the total available workdays for the period looked at.
Been messing around with DISTINCTCOUNT Function in Measures, Along with MAX and MIN functions, but I can't seem to get a consistent return that examines the time interval and returns the number of workdays.
Is there a formula I'm missing in the Power Pivot Measures? Seems like a simple thing, but I can't seem to find it.
The Time Window I am looking at is from 7AM to 3:30 PM or 510 minutes in a day.
Any ideas?
My pivot table examines how many minutes a particular operating room is used during a week, month, Day of the Week. I can calculate the number of minutes the room was used without difficulty, but am having trouble with returning the overall number of days it was available for use.
For Example If I'm looking at January 2022, it would return 21 days. Or If I'm looking at the second week in January, it would return 5 days, etc. My pivot table finds the number of days in the period that the room was occupied using the DISTINCTCOUNT function which is the numerator. But I can't seem to find the denominator which would be the total available workdays for the period looked at.
Been messing around with DISTINCTCOUNT Function in Measures, Along with MAX and MIN functions, but I can't seem to get a consistent return that examines the time interval and returns the number of workdays.
Is there a formula I'm missing in the Power Pivot Measures? Seems like a simple thing, but I can't seem to find it.
The Time Window I am looking at is from 7AM to 3:30 PM or 510 minutes in a day.
Any ideas?
