Forum Discussion

amuola's avatar
amuola
Icon for Helper II rankHelper II
8 years ago

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

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-p/499255#M232871

 

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

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support

    amuola,

     

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

    • amuola's avatar
      amuola
      Icon for Helper II rankHelper II

      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

      XXX123Log on01.08.2018 00:00
      XXX123Activity 102.08.2018 00:00
      XXX123Activity 203.08.2018 00:00
      XXX123Completed04.08.2018 00:00
      XXX123Completed05.08.2018 00:00
      XXX456Log on06.08.2018 00:00
      XXX456Activity 107.08.2018 00:00
      XXX456Completed08.08.2018 00:00
      XXX456Completed09.08.2018 00:00
      YYY789Log on10.08.2018 00:00
      YYY789Activity 111.08.2018 00:00
      YYY789Completed12.08.2018 00:00
      YYY101Log on19.08.2018 00:00
      YYY101Activity 120.08.2018 00:00
      YYY101Completed21.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