Create a custom field that calculates duration in hours between rows in the same column

[Anonymous]

Hi All,

I am very new to power bi and all i can only use power query right now. I have a sample data below and i want to calculate the duration in  hours between the current row and the previous row by the product_Category and date field. For example,  i would expect the duration between the 1st and 2nd row for Doritos to give me say 21 hrs even though they have different dates . Please use the main table. Thanks so much for your help

    

[Anonymous · Accepted solution]

Hi Alexis,

Thanks for taking the time to respond to my question. I carried out the steps as instructed but i got zeros when i calculated the duration. Could you please clarify step 3 further? Did you mean to to join on all 3 fields? If yes, that's waht i did. Thanks.

3. Self-merge on Product_Category and index 0 to index 1 to pull prior DateTime value.

[AlexisOlson]

This is a bit of a pain but I'd sketch it like this:

1. Create a custom column to combine Date and Time into a single DateTime.

2. Create two indices (one from 1 and one from 0).

3. Self-merge on Product_Category and index 0 to index 1 to pull prior DateTime value.

4. Calculate the difference in time between DateTime and prior DateTime for your duration.

Check the post and related links here for more detail on steps 2 and 3.

[Anonymous · Accepted solution]

Hi Alexis,

Thanks for taking the time to respond to my question. I carried out the steps as instructed but i got zeros when i calculated the duration. Could you please clarify step 3 further? Did you mean to to join on all 3 fields? If yes, that's waht i did. Thanks.

3. Self-merge on Product_Category and index 0 to index 1 to pull prior DateTime value.

[AlexisOlson · Accepted solution]

Merge on two columns. Match Product_Category and one index to Product_Category and the other index. This is just a fancy way of getting the previous row within the same category.