Need Advice: How to Calculate Date Diff between Two Exact Event

[Irwan]

Hello Power BI Experts,

I need an advice about my dataset.

Below is my data.

My need is to find the next row that contains the exact string (description) for example "replace" with the exactly same equipment number as both in yelow color below, then substract the date of that row with date of current row (yellow color). And the process continue until last row of data (next date of the data will be substract to previous date of the data).

To make it simple, I want to calculate date difference between two events with exact description and equipment number.

Thank you and really appreciated for the help.

[Irwan]

Hello @Ahmedx 

thanks, everything is working, got all my need to shape my database.

thank you and really appreciated for all the help.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiQZUXnY1lqz0ovDw?e=ufVd4q

[Irwan]

Hello @Ahmedx 

one last question, can we move the data in "Count of Days" into separate table or column accordingly to its "ID" and then sort it out in ascending order?

Thank you again.

[Ahmedx]

pls try

How to Calculate Date Diff between Two Exact Event.pbix

[Irwan]

hello @Ahmedx 

thanks, it moved to new table, however the days are summed in the new table according to ID. Is there a way not to sum the days in the new table?

the expected new table is going to be like this 

ID	Day
6000000002638	298
6000000002638	377

and also is it possible to automatically sorted "day" column in ascending order after it moved to new table?

Thank you again, really appreciated for the help.

[Ahmedx]

pls try this

[Irwan]

Hello @Ahmedx 

It works perfectly.

Thank you and really appreciated for all the help.

[Irwan]

Hello @Ahmedx 

thank you for the code, it works as my need.

Thanks again for the support.

[Ahmedx]

Share some data to work with (in a format the can be pasted in an MS Excel file) and show the expected result.

[Irwan]

hello @Ahmedx ,

here is sample of the data to make it simpler.

The expected result is

1. 298 (from 16-Dec-19 till 9-Oct-20), days difference between line 2 and line 9.

2. 377 (from 9-Oct-20 till 16-Dec-19), days difference between line 9 and line 13.

Those three events have exactly same description and equipment number.

1	600000001974	Replace pulley	8-Oct-19
2	600000002638	Replace motor	16-Dec-19
3	600000083190	Replace pulley	27-Jan-20
4	600000002693	Replace pulley	12-May-20
5	600000002616	Replace pulley	26-May-20
6	600000083190	Replace pulley	6-Jun-20
7	600000002638	Replace pulley	7-Jul-20
8	600000003106	Replace pulley	21-Sep-20
9	600000002638	Replace motor	9-Oct-20
10	600000002693	Replace pulley	29-Jul-21
11	600000002645	Replace pulley	19-Aug-21
12	600000002624	Replace pulley	15-Oct-21
13	600000002638	Replace motor	21-Oct-21

Thank you and appreciated with the help.