Difference between two columns and finding the Average it

[Charu]

Dear All,

Please help me to write the formula to find the difference between two date columns and then take the average of that.

Below is the sample

LAST_UPDATED_DATE	CREATION_DATE	DAY DIFF
10/4/2018 0:00	9/30/2018 0:00	4	Average =1.428571429
10/1/2018 0:00	9/30/2018 0:00	1
10/1/2018 0:00	9/30/2018 0:00	1
10/1/2018 0:00	9/30/2018 0:00	1
10/1/2018 0:00	9/30/2018 0:00	1
10/1/2018 0:00	9/30/2018 0:00	1
10/1/2018 0:00	9/30/2018 0:00	1

Average to be shown in the CARD visual.

Thanks in advance

[Greg_Deckler]

Well, you should be able to create a column:

DAY DIFF = DATEDIFF([LAST_UPDATED_DATE],[CREATION_DATE],DAY)

And then a measure:

Average of DAY DIFF = AVERAGEX('Table',[DAY DIFF])

[Charu · Accepted solution]

Hi @Greg_Deckler

Calculated Column for DATEDIFF is throwing an error saying "Function DATEDIFF is not allowed as part of calculated Column DAX expressions on DirectQuery models.

So I tried the DATEDIFF with Measure and it's accepting only with aggregate function like MIN,MAX etc,like below

1.DAY DIFF = DATEDIFF(MIN('Table1'[LAST_UPDATED_DATE]),MIN('Table1'[CREATION_DATE]),DAY)

2.AVG OF DAY = AVERAGEX('Table1','Table1'[DAY DIFF])

It's working fine.

Thank you so much for your timely support.

[Greg_Deckler]

Glad you got it sorted out. Yeah, DirectQuery causes some issues with certain DAX functions.

[AlB]

Hi @Charu

Try this for your measure:

DiffAvg =
AVERAGEX ( Table1, Table1[ LAST_UPDATED_DATE] - Table1[CREATION_DATE] )

[Charu]

Hi @AlB

When I create the AverageX measure with two date fields received the below error message

ORA-00904:"t6"."CREATION_DATE":invalid identifier.The exception was raised by the IDbCommand interface