Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 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 )
Best Regards,
Herbert
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |