Average of DateDIFF in Power BI

[nmckeown1]

I'm trying to calculate an average measure for the datediff column 'Engagement Time in the service (in days)'

The calculated table is:

Total Days in service =

SUMMARIZECOLUMNS(

'Aggregated Daily'[client_key],

"Session Start Date", MIN('Aggregated Daily'[start_date]),

"Discharge Date", MAX('Aggregated Daily'[discharge_date]),

"Engagement Days",DATEDIFF(MIN('Aggregated Daily'[start_date]),MAX('Aggregated Daily'[discharge_date]),DAY))

The column for engagement days is calulating the datediff correctly.

I then tried to create an average of engagement days per client:

Average engagement time =

AVERAGE('Engagement Date'[Engagement Time in sessions])

This gives incorrect average when compared to exporting the data into excel. Average is 23.36 in PB and 45.87 in excel (which I checked manually and is correct average). The column contains all whole numbers and the format is all dates and whole numbers.

[nmckeown1]

Update:

I created a measure for total engagement days, instead of using the column for this. Calculates the same values.

Engaged =

CALCULATE(SUM('table'[Engagement Time in sessions]))

I then used the dax measure to calculate an average:

average days engaged =

AVERAGEX(

    VALUES('Engagement Date'[client_key]),

    [Engaged]

)

This poplulated an average of '47.45' days, which brings me closer to the excel average (45.87) - but still not matching exactly?

[Muhammad_Ahmed · Accepted solution]

divide( 
SUM('table'[Engagement Time in sessions]), 
countrows(VALUES('Engagement Date'[client_key])
)

Since averagex ignore the blank values, can you try this.

[Muhammad_Ahmed]

because I will have to write some dax code and check where the difference is coming.

[Muhammad_Ahmed]

can you share the pbix.
I have to look.

[Anonymous]

Why do need the file?