Average NetworkDays

[CristianConto]

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

[v-chuncz-msft · Accepted solution]

@CristianConto,

You may try to select Pivot Column in Query Editor, then use AVERAGEX Function in DAX.