Calculate difference between latest date and previous date using a measure

[NB1]

Hi everyone,

As a way to work out how well a schedule is working, I want to work out the difference between the date a ship calls into a port, and the previous date another ship called into that same port. I want to work it out using a measure, rather than a calculate column, if possible.

The screenshot below show the Port in Column 1, the name of the service in Column 2, and the date of arrival in the port in Column 3. So for example, I want to calculate the number of days between the two calls into THSRI on 04/09/2018 and 22/09/2018.

Any ideas?

Thanks!

NB1

[v-shex-msft · Accepted solution]

HI @NB1,

You can use below measure to calculate diff between current and previous ship date:

Diff =
VAR currDate =
    MAX ( Table[Date] )
VAR previous =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALLSELECTED ( Table ), [Date] < currDate ),
        VALUES ( Table[Ship Name] ),
        VALUES ( Table[Port Name] )
    )
RETURN
    DATEDIFF ( currDate, previous, DAY )

Regards,
Xiaoxin Sheng