Latest Enterprise DNA Initiatives

Average measure not returning the correct results

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.

Date Diff Avg 1

Date Diff Avg 2

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

@jprlimey

Can you please check below Avg Phase 1 numbers ?

image

Date Diff Rev-2.pbix (125.7 KB)

@Rajesh

Thank you for the quick response.

Yes, the numbers look correct and have incorporated the updated DAX into my report and everything looks good.

It’s amazing how simple the solution was by removing the ‘Date’ [Month] and replacing it with ‘Data’ [NPC No], I feel kinda dumb!

Thanks for your assistance

Best Regards
J