Calculate difference between previous date of same column using DAX

[POSPOS]

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.

[v-xinruzhu-msft · Accepted 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.

[ThxAlot]

Interval days.pbix

An index column is added to differentiate duplicated dates.

[POSPOS]

@ThxAlot  - Have you created index using DAX? Is there a way I can create index column using DAX.
Could you share you pbix pls.