This is a continuation of a previous post, please refer to
DateDiff between dates in different rows
Currently I have evolved the DAX measure for DATEDIFF by exclusion of weekend days
Weekdays Days Phase 1 =
VAR _StartDateTime =
CALCULATE(
MAX( 'Data'[ Assigned On] ),
'Data'[Step Name] = "Start Step"
)
VAR _EndDateTime =
CALCULATE(
MAX('Data'[ Last Action Date] ),
'Data'[Step Name] = "Phase 1"
)
VAR _Phase1 =
IF ( // check if end date is blank, return blank if true else calculate days between
_EndDateTime = BLANK() , BLANK() ,
DATEDIFF( _StartDateTime , _EndDateTime , HOUR )
)
VAR _WeekEndDay = // determine if weekend days are between start and end date
IF ( _EndDateTime = BLANK() , BLANK() , // check if end date is blank, return blank if true else calculate weekend days
CALCULATE(
COUNTROWS( 'Date') ,
'Date'[IsWorkingDay] = FALSE() ,
DATESBETWEEN( 'Date'[Date] , _StartDateTime , _EndDateTime )
)
)
VAR _Result =
_Phase1 - _WeekEndDay
RETURN
DIVIDE( _Result , 24 , BLANK() ) // convert to decimal days
I also added another DAX measure to get the real total
Total Phase 1 =
VAR _total =
SUMMARIZE(
'Data' ,
'Data'[NPC No] ,
"@Sum Total" , [Weekdays Days Phase 1]
)
RETURN
IF( HASONEVALUE( 'Data'[NPC No] ) ,
[Weekdays Days Phase 1] ,
SUMX( _total , [@Sum Total] )
)
The resulting images shown below show that everything appears to be working nicely.
However when it comes to getting the average for each month as shown in the last image from above, it is not returning the correct results, the DAX measure for average is below.
Avg Phase 1 =
AVERAGEX(
SUMMARIZE(
Data ,
'Date'[Month] ,
"@Avg" , [Weekdays Days Phase 1]
) ,
[Weekdays Days Phase 1]
)
Where am I going wrong here? Any help would be appreciated.
Attached is the pbix file
Date Diff Rev-2.pbix (125.6 KB)
Regards
J
