Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

@Locke

 

Have you tried above solutions?

 

Best Regards,

Herbert

v-haibl-msft
Employee
Employee

@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
Super User

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!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.