Forum Discussion
Average NetworkDays
Hello all.
I've been trying to create this function for last two days... I'm getting crazy....
O tried a lot of different ways using DAX but nothing is working as expected here..
I need to calculate the average networkdays (including holidays). So, here is an example of my table, and I have a join to a CalendarTable by OrderDate. This table has multiple orders, each order has a status assigned. I need to create this average for the first date with Status 1 or 2 and the first date of Status 4.
I may have all status in the same day (in this case the Delta would be 0) or and I can have the Status 1 in one week and two weeks later the status 4, in this case, i need to calculate the network days (Excluding Saturday, Sunday and Holidays if it has).
Any Idea about how to make it work?
Regards.
| OrderNumber | OrderDate | Status |
| 1 | 1/9/2018 | Status 1 |
| 1 | 1/9/2018 | Status 2 |
| 1 | 1/11/2018 | Status 3 |
| 1 | 1/14/2018 | Status 4 |
| 2 | 1/20/2018 | Status 1 |
| 2 | 1/21/2018 | Status 2 |
| 2 | 1/22/2018 | Status 3 |
| 2 | 1/23/2018 | Status 4 |
| 2 | 1/24/2018 | Status 6 |
| 2 | 1/25/2018 | Status 7 |
| 2 | 1/26/2018 | Status 8 |
| 3 | 1/18/2018 | Status 2 |
| 3 | 1/19/2018 | Status 3 |
| 3 | 1/20/2018 | Status 3 |
You may try to select Pivot Column in Query Editor, then use AVERAGEX Function in DAX.
1 Reply
- v-chuncz-msftCommunity Support
You may try to select Pivot Column in Query Editor, then use AVERAGEX Function in DAX.