Forum Discussion

gabrielkvd96's avatar
gabrielkvd96
Frequent Visitor
3 years ago

Calculate average datediff between two process steps

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 NumberProcess stepProcess Date
7131842Out On Auction2022-10-26
7131842Buy-Option2022-12-08
7131905Out On Auction2022-12-07
7131905Buy-Option2022-12-12

 

My Expected Outcome would like to be:

 

Object NumberAverage LeadtimeYear - Month
713184243202212
71319055202212


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:

 

 

2 Replies