@Jetuke_ES,
OK, I think I got it working, and it’s an interesting scenario for the use of the NETWORKDAYS function, which I first applied to the Dates table. Here’s the measure that does the heavy lifting. Basically, it computes the fractional days on the min and max ends (zeroing out one or both if they fall on weekends) and then adding those two fractional days to the full net work days in between:
Total NetWorkDays =
VAR FirstDay = [Min Created On]
VAR YrMin = YEAR( FirstDay )
VAR MoMin = MONTH( FirstDay )
VAR DyMin = DAY( FirstDay )
VAR FirstDayWeekend =
SWITCH( TRUE(),
WEEKDAY( FirstDay ) = 7, 0,
WEEKDAY( FirstDay ) = 1, 0,
1
)
VAR LastDay = [Max Completed On]
VAR YrMax = YEAR( LastDay )
VAR MoMax = MONTH( LastDay )
VAR DyMax = DAY( LastDay )
VAR LastDayWeekend =
SWITCH( TRUE(),
WEEKDAY( LastDay ) = 7, 0,
WEEKDAY( LastDay ) = 1, 0,
1
)
VAR FirstDayEnd =
DATE( YrMin, MoMin, DyMin ) + TIME(23, 59, 59 )
VAR LastDayBegin =
DATE( YrMax, MoMax, DyMax ) + TIME( 0, 0, 0 )
VAR SecondsPerDay = 24 * 60 * 60
VAR FirstDayDays =
DIVIDE(
DATEDIFF(
FirstDay,
FirstDayEnd,
SECOND
),
SecondsPerDay,
0
)
VAR LastDayDays =
DIVIDE(
DATEDIFF(
LastDayBegin,
LastDay,
SECOND
),
SecondsPerDay,
0
)
VAR NetWorkDaysDiff =
[Networkdays Created On] - [Networkdays Completed On]
VAR FullNetWorkDays =
IF(
NetWorkDaysDiff < 2,
0,
NetWorkDaysDiff - 1
)
RETURN
(FirstDayDays * FirstDayWeekend) + FullNetWorkDays + (LastDayDays * LastDayWeekend)
Then computing the average is a straightforward matter of substituting the measure above into your prior AVERAGEX calculation:
Average NetWorkDays Elapsed =
VAR vTable =
FILTER(
Data,
Data[Keep Record] = "Yes"
)
VAR vTable2 =
ADDCOLUMNS(
SUMMARIZE(
vTable,
Data[Overall Process]
),
"Total NWD", [Total NetWorkDays]
)
RETURN
AVERAGEX(
vTable2,
[Total NWD]
)
I hope this is helpful. Full solution file attached below.