cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Locke
Regular Visitor

Time calculation based on different rows -> earlier?

Hi Guys,

 

I have read the post on the earlier function, but can't seem to be able to find the correct formula for my case.

 

I have a Table with offer changes, each row represents a status change. To give a short example table:

 

id | timestamp | offercount

123 | 13.10.2016 12:00 | 1

123 | 13.10.2016 11:00 | 2

123 | 13.10.2016 10:00 | 1

123 | 13.10.2016 9:30 | 2

456 | 13.10.2016 11:00 | 1

456 | 13.10.2016 10:30 | 3

...

 

 

What I would like to do, is calculate the time difference, when the status changes from >1 to 1. 

123 | 13.10.2016 12:00 | 1 | 1:00

123 | 13.10.2016 11:00 | 2

123 | 13.10.2016 10:00 | 1 | 0:30

123 | 13.10.2016 9:30 | 2 

456 | 13.10.2016 11:00 | 1 | 0:30

456 | 13.10.2016 10:30 | 3

...

 

The idea was to use the earlier funtion and combine this with Datediff. But I can't get the right function. Any ideas?

 

 

My other solution was having two tables: one where the offercount is always 1 and the other, where the count is >1. Then calculate with the max values: = CALCULATE(MAX('AMZ_1'[time])-max('AMZ_>1'[time]))

The hours seem to match. But the days are not taken into account. And maybe not a very elegant solution.

 

 

Any ideas or tips?

 

Thanks in advance!

Tanja

 

 

3 REPLIES 3
v-haibl-msft
Microsoft
Microsoft

@Locke

 

Have you tried above solutions?

 

Best Regards,

Herbert

v-haibl-msft
Microsoft
Microsoft

@Locke

 

You can try to create an index column in Query Editor and then a calculated column with following formula. LOOKUPVALUE and DATEDIFF are used here, and the time unit here is hour.

 

Time calculation based on different rows_1.jpg

 

time difference = 
IF (
    'Table'[offercount] = 1
        && LOOKUPVALUE ( 'Table'[offercount], 'Table'[Index], 'Table'[Index] + 1 )
            > 1,
    DATEDIFF (
        LOOKUPVALUE ( 'Table'[timestamp], 'Table'[Index], 'Table'[Index] + 1 ),
        'Table'[timestamp],
        SECOND
    )
        / 3600
)

Time calculation based on different rows_2.jpg

 

Best Regards,

Herbert

OwenAuger
Super User I
Super User I

Hi @Locke

 

Here is a calculated column to get you started - see if it does what you want.

 

It calculates difference from previous timestamp for current id if offercount = 1.

It doesn't explicitly check whether most recent offercount >1, but you could build that in as well.

 

Timestamp difference =
IF (
    'Table'[offercount] = 1,
    VAR Current_timestamp = 'Table'[timestamp]
    RETURN
        Current_timestamp
            - CALCULATE (
                MAX ( 'Table'[timestamp] ),
                ALLEXCEPT ( 'Table', 'Table'[id] ),
                'Table'[timestamp] < Current_timestamp
            )
)

Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors