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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ZadquielBazan
New Member

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 records from a fact table, so I read a bit and learned about those WINDOW functions DAX got and could help me achieving such task. But what I'm getting is the same value for both my initial and final time (EARLIEST TIME and LATEST TIME, respectively), so when I use DATEDIFF I'm always getting 0 as a result. To analize where I'm failing in my measure, decided to count the rows from this WINDOW function, which I expect is 2 cause I'm making up it from the current and previous row but returns 1 like I'm only taking account the current row.

ZadquielBazan_0-1729562599359.png


This is my measure for EARLIEST TIME, for LATEST TIME syntax is the same just I use MAX instead of MIN within CALCULATE. Regarding to ROW, I use the same VAR, but return COUNTROWS instead of a CALCULATE.

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


PARTIONBY is used cause I would want to know the total time between the same type of operation in the same day and sometimes people can make a different one in between two operations of the same class.

Tried using OFFSET and INDEX, but the results are the same.

Something weird is records who got 2 rows in the WINDOW function, but returns the same time for both measures regardless.
ZadquielBazan_2-1729562691090.png


I did the same measure in a DAX Query and it worked nicely, but don't know how to make it works properly with my measure. This is the behaviour I'm expecting.
ZadquielBazan_3-1729562787101.png


How can I fix this?

Thank you for your help beforehand

1 ACCEPTED SOLUTION
ZadquielBazan
New Member

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!

View solution in original post

2 REPLIES 2
ZadquielBazan
New Member

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

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 





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

Proud to be a Super User!





Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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