Average Measure with filter

[Anonymous]

Hi,

I want to calculate the average days it take to complete an order with product type 'Computer'. How can I calculate that in a measure?

This is a sample of my data:

OrderID	OrderDate	OrderCompleteDate	Product
1	1-11-2021	5-11-2021	Computer
2	4-11-2021	7-11-2021	Computer
3	7-11-2021	11-11-2021	Computer

[Ashish_Mathur]

Hi,

You may create a calculated column formula to calculate the difference between the 2 date columns.  Give a heaing to that column as Days.  Then write this measure

Measure = average9Data[Days])

To your table visual, drag Product and the Measure. 

[CNENFRNL]

[ryan_mayu · Accepted solution]

@Anonymous 

you can create a column and measure

days = DATEDIFF('Table'[OrderDate      ],'Table'[OrderCompleteDate     ],DAY)

Measure = AVERAGEX(FILTER('Table','Table'[Product     ]="Computer"),'Table'[days])

or  you can create a measure directly.

Measure 2 = 
VAR tbl=ADDCOLUMNS(FILTER('Table','Table'[Product     ]="Computer"),"day2",DATEDIFF('Table'[OrderDate      ],'Table'[OrderCompleteDate     ],DAY))
return AVERAGEX(tbl,[day2])

pls see the attachment below.

[Anonymous]

@ryan_mayu Thanks, question: in the second measure, why do you define "day2"?

[ryan_mayu]

@Anonymous 

like @manikumar34 mentioned, just want to differerntiate from the day column in the first solution.

[manikumar34]

@Anonymous ,

days2 is defined here as a column for the difference of those tow date columns. You can use othe column name instaed. One first measure dates difference created as a column whereas on 2nd calculation written as a measure, so he is using variable and ADDCOLUMNS functions to define that on measure and use that on AVERAGEX