Days between last and second last order date

[dgdgdg122db]

Hi , I want to show the average days between the last order and the second last order in my report.

order no	order date	client no	Datediff
A123	1/1/2020	AAA1
B123	1/2/2020	AAA1
C123	1/10/2020	AAA1
D123	1/12/2020	AAA1	2
order no	order date	client no	Datediff
A321	1/1/2020	BBB1
B321	1/1/2020	BBB1	0

[v-zhenbw-msft · Accepted solution]

Hi @dgdgdg122db ,

We can use a calculate column or a measure to meet your requirement.

1. The calculate column solution, we can create a column using the following formula.

Column = 
var last_date = CALCULATE(MAX('Table'[order date]),FILTER('Table','Table'[client no]=EARLIER('Table'[client no])))
var last_second = CALCULATE(MAX('Table'[order date]),FILTER('Table','Table'[client no]=EARLIER('Table'[client no])&&'Table'[order date]<last_date)) 
var dif_ = DATEDIFF(last_second,last_date,DAY)
return
IF(ISBLANK(dif_),0,dif_)

2. The measure solution, you can refer the following formula.

Measure = 
var last_date = CALCULATE(MAX('Table'[order date]),FILTER(ALLSELECTED('Table'),'Table'[client no]=MAX('Table'[client no])))
var last_second = CALCULATE(MAX('Table'[order date]),FILTER(ALLSELECTED('Table'),'Table'[client no]=MAX('Table'[client no])&&'Table'[order date]<last_date)) 
var dif_ = DATEDIFF(last_second,last_date,DAY)
return
IF(ISBLANK(dif_),0,dif_)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@dgdgdg122db , can create a desc rank and sum Rank 1 and 2

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...