Calculate average datediff between two process steps

[gabrielkvd96]

Hello Power-BI community, 

I have an issue where I try to calculate the average leadtime between two process steps(Out on Auction -> Buy-Option) within our workflow. The issue occurs whenever the datediff between two process steps are in two different months. In this case, I would like to always refer the datediff average to the latest date/month which the lastest process step occured,  which in this case is "Buy-Option". Here is the outcome of the DAX I have tried, but for Object Number 7131842 the datediff cannot be placed in Year-Month 202212 which object number 7131905 can since the two process steps occured in the same month. See sample date below:

Object Number	Process step	Process Date
7131842	Out On Auction	2022-10-26
7131842	Buy-Option	2022-12-08
7131905	Out On Auction	2022-12-07
7131905	Buy-Option	2022-12-12

My Expected Outcome would like to be:

Object Number	Average Leadtime	Year - Month
7131842	43	202212
7131905	5	202212

RELATIONS:

Here is the DAX im using at the moment:

Out on Auction -> Buy-Option =

AVERAGEX(

KEEPFILTERS(VALUES('Dim Vehicle Details'[Object Number])),

CALCULATE(

IFERROR(

DATEDIFF(

(CALCULATE(MAX(Process[Process Date]), 'Dim Process Step'[Process Step]="Out On Auction")),

(CALCULATE(MAX(Process[Process Date]), 'Dim Process Step'[Process Step]="Buy-Option")),

DAY),

"")

)

)

This is my outcome by using the DAX above:

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[gabrielkvd96]

Hi Ibdenlin, 

Thank you for the reminder! 

Please see updated post.

Thank you!