How to find the last timestamp in a column for a certain activity

[amuola]

Hi

I would like to count the number of days between start and finish of a certain process as illustrated in the table below: “Log on” 8.11.18 and “Completed” 8.15.18 = 4 days. The problem is that the activity “Completed” appears in two different rows (the activity is for some reason done twice) and I will make sure that the formula counts the last one of the two “Completed rows”.

ActivityTimestamp

Log on	8.11.18 0:00
Activity 1	8.12.18 0:00
Activity 2	8.13.18 0:00
Completed	8.14.18 11:08
Completed	8.15.18 11:32

Any idea?

Regards Amund

[deldersveld · Accepted solution]

You should be able to focus in on the final datetime using LASTDATE.

The measure would be something like this:

Measure =
VAR MinDate = CALCULATE(FIRSTDATE('Table'[ActivityTimestamp]),'Table'[Activity]="Log on")
VAR MaxDate = CALCULATE(LASTDATE('Table'[ActivityTimestamp]),'Table'[Activity]="Completed")
RETURN DATEDIFF(MinDate,MaxDate,DAY)

[amuola]

Hi

Thank you so much, this works!

However, I have another column in the original table which is booking number – assuming this is a repetitive process for each booking number (adding one more column = Booking number), hence I would like to get the days for each distinct bookingnumber. Do you have any suggestions how to modify the measure in order to take this element into account?

Regards Amund