Days between duplicates

[JoonasOP]

Hi all!

I have list of repair orders with each having unique repair order number and serial number associated to product.

I want to count days between each duplicate record when same serial number has been associated with other repair order previously. Hopefully I was able to explain my problem clearly. 😁

Example:

Order num.                Serial                Repair completed               Days from previous order

0001                          12345                10/10/2021                        N/A

0002                          54321                12/10/2021                        N/A

0003                          13523                15/10/2021                        N/A

0004                          12345                16/10/2021                        6

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below.

It is for creating a new measure.

Days from previous order: =
VAR currentserial =
MAX ( Data[Serial] )
VAR currentordernumber =
MAX ( Data[Order num] )
VAR currentdate =
MAX ( Data[Repair completed] )
VAR previousdate_sameserial =
MAXX (
FILTER (
ALL ( Data ),
Data[Serial] = currentserial
&& Data[Order num] < currentordernumber
),
Data[Repair completed]
)
RETURN
IF (
HASONEVALUE ( Data[Order num] ),
IF (
ISBLANK ( previousdate_sameserial ),
"N/A",
INT ( currentdate - previousdate_sameserial )
)
)

[JoonasOP]

Woah! Thanks! This one did it! 🙂