Forum Discussion
Post PartisanCalculate difference between previous date of same column using DAX
Hi All,
I have a requirement to calculate the difference between date of the current line to the previous line of the same column.
Please find sample data below:
| Protocol | Received | Status | Date |
| A | 6/30/2022 | Assigned | 11/7/2022 |
| A | 6/30/2022 | In-Progress | 1/18/2023 |
| A | 6/30/2022 | Pre-Review | 2/6/2323 |
| A | 6/30/2022 | Agenda | 2/6/2023 |
| A | 6/30/2022 | Pre-Review | 2/15/2023 |
| B | 7/10/2023 | Assigned | 7/15/2023 |
| B | 7/10/2023 | In-Progress | 7/15/2323 |
| B | 7/10/2023 | Agenda | 8/1/2023 |
| B | 7/10/2023 | Pre-Review | 8/10/2023 |
| B | 7/10/2023 | Pre-Review | 8/15/2023 |
Expected Output:
| Protocol | Received Date | Status | Effective Date | No. of days |
| A | 6/30/2022 | Assigned | 11/7/2022 | 130 |
| A | 6/30/2022 | In-Progress | 1/18/2023 | 72 |
| A | 6/30/2022 | Pre-Review | 2/6/2023 | 19 |
| A | 6/30/2022 | Agenda | 2/6/2023 | 0 |
| A | 6/30/2022 | Pre-Review | 2/15/2023 | 9 |
| B | 7/10/2023 | Assigned | 7/15/2023 | 5 |
| B | 7/10/2023 | In-Progress | 7/15/2023 | 0 |
| B | 7/10/2023 | Modified | 8/1/2023 | 17 |
| B | 7/10/2023 | Pre-Review | 8/10/2023 | 9 |
| B | 7/10/2023 | Modified | 8/15/2023 | 5 |
1. First value of a group will be the received date minus effective date
2. The other values will be difference from the previous date of the effective date column.
I followed the blog below but this is not working when the dates are same as well the status are getting aggregated instead of showing it separately.
DAX Fridays! #85: Difference between dates in the same column
Could someone pleas advise how this can be solved using DAX.
Thank you.
Hi POSPOS
You can add a index column in power query first and apply it
Then create a calculated column
Column = var a=FILTER('Table',[Protocol]=EARLIER('Table'[Protocol])) var b=MINX(FILTER(a,[Date]=MINX(a,[Date])),[Index]) return IF([Index]=b,DATEDIFF([Received],[Date],DAY),DATEDIFF(MAXX(FILTER(a,[Index]=EARLIER('Table'[Index])-1),[Date]),[Date],DAY))Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- ThxAlot
Super User
Hi POSPOS
You can add a index column in power query first and apply it
Then create a calculated column
Column = var a=FILTER('Table',[Protocol]=EARLIER('Table'[Protocol])) var b=MINX(FILTER(a,[Date]=MINX(a,[Date])),[Index]) return IF([Index]=b,DATEDIFF([Received],[Date],DAY),DATEDIFF(MAXX(FILTER(a,[Index]=EARLIER('Table'[Index])-1),[Date]),[Date],DAY))Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
