Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
@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...
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
108 | |
96 | |
95 | |
74 | |
71 |