Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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.
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.
An index column is added to differentiate duplicated dates.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
@ThxAlot - Have you created index using DAX? Is there a way I can create index column using DAX.
Could you share you pbix pls.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
109 | |
94 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |