Calculating time/ days for each activity in a process based on a critertira from another column

[amuola]

Hi,

I posted a similar case last week however it is actually a bit more complicated:

I need to figure out the time (in days) between different “Transactions” within each Booking number in the below table. For each Booking number there might be several rows with the same Transaction and in that case the measure needs to pick the last one.

E.g. Booking number 123 – I want to calculate the number of days from Order is created (Timestamp 01.08.2018) until Transaction “Completed” (Timestamp 05.08.2018). In this case there is two rows with “Completed” and the measure must pick the last one.

This might be a part of the solution...?

https://community.powerbi.com/t5/Desktop/How-to-find-the-last-timestamp-in-a-column-for-a-certain/m-...

This solves partially the problem, calculating the time from “Start to finish” – in addition the calculation must split that number of days into each booking, and taking into consideration the last one if there is several rows with the same Transaction (e.g Completed for booking nr. 123 =pick the last of the two "Completed"

Appreciate any suggestion to solve this!

Regards

Amund

[v-chuncz-msft]

@amuola,

A measure always operates on aggregations of data under the evaluation context. No modification is required to the measure.

[amuola]

Hi,

Thank you for your comment! But I can’t still understand this – now I have this table showing different transactions on each of the booking number. Each of the booking number is related to a department and I want to see the average time it takes to complete a booking. If I filter the measure as it is on Booking number it gives me the right number of days per booking, however, on an aggregated level it does not work. From the below table, I cannot see how the measure can give me the average numbers per booking or per Department. Any suggestions how to modify the measure? Or do I have to use calculated column to solve this?

Dep.Booking nr.TransactionTimestamp

XXX	123	Log on	01.08.2018 00:00
XXX	123	Activity 1	02.08.2018 00:00
XXX	123	Activity 2	03.08.2018 00:00
XXX	123	Completed	04.08.2018 00:00
XXX	123	Completed	05.08.2018 00:00
XXX	456	Log on	06.08.2018 00:00
XXX	456	Activity 1	07.08.2018 00:00
XXX	456	Completed	08.08.2018 00:00
XXX	456	Completed	09.08.2018 00:00
YYY	789	Log on	10.08.2018 00:00
YYY	789	Activity 1	11.08.2018 00:00
YYY	789	Completed	12.08.2018 00:00
YYY	101	Log on	19.08.2018 00:00
YYY	101	Activity 1	20.08.2018 00:00
YYY	101	Completed	21.08.2018 00:00

The measure gives this result (which is correct) - but how do I find an average on an aggregated level? e.g. how to find the average pr booking number or average per department?

Regards

Amund

[v-chuncz-msft]

@amuola,

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/monthly-average-of-daily-sums/m-p/291451#M128396