Forum Discussion

ZadquielBazan's avatar
ZadquielBazan
New Member
1 year ago
Solved

Calculating time between two different moments using WINDOW

Hi, this is my first post here and I want to start greeting everybody here in the community! I have this issue with a measure: I'm intending to compute the total time between two consecutive recor...
Need Help
Tips and Tricks
  • ZadquielBazan's avatar
    ZadquielBazan
    1 year ago

    Hi rajendraongole1 , your solution didn't worked, but at the end I managed to figure it out the issue: just was a wrong definition of the table in the current context. Solved it by using ALLSELECTED('TABLE') in relation instead of 'TABLE'.

    Thank you for your help regardless!

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

Hi ZadquielBazan - can you modify your dax function with earliest as below:

EARLIEST TIME =
VAR PrevTimeWindow =
OFFSET(-1, REL, 0, REL, 'TABLE', , DEFAULT, PARTITIONBY('TABLE'[TIPO], 'TABLE'[USR], 'TABLE'[FECHA]))
RETURN
CALCULATE(MIN('TABLE'[HORA]), PrevTimeWindow)

 

to find the latest time use below calculation

 

LATEST TIME =
VAR PrevTimeWindow =
OFFSET(-1, REL, 0, REL, 'TABLE', , DEFAULT, PARTITIONBY('TABLE'[TIPO], 'TABLE'[USR], 'TABLE'[FECHA]))
RETURN
CALCULATE(MAX('TABLE'[HORA]), PrevTimeWindow)

 

Once you have the "Earliest Time" and "Latest Time" working correctly, you can use DATEDIFF to compute the total time between the two , Make sure the window function captures both the current and the previous row.

 

TIME_DIFF =
DATEDIFF([EARLIEST TIME], [LATEST TIME], SECOND)

 

Hope this works