Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate difference between latest date and previous date using a measure

 

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous,

     

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous,

     

    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