Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II
Helper II

DAX Calculating Run Time from Timestamps

Hello, I need help writing a DAX that returns time intervals using timestamps. The table is below:

object_namesensor_idload_presenttimestampRun Increments
Stand 8111566532803 
Stand 8511566532805 
Stand 8811566532805 
Stand 81511566532806 
Stand 8211566532809 
Stand 8611566532810 
Stand 8911566532810 
Stand 8301566532822 
Stand 81601566532823 
Stand 81701566532823 
Stand 8401566532823 
Stand 8701566532823 
Stand 81201566532824 
Stand 81401566532824 
Stand 8101566532827 
Stand 8501566532829 
Stand 8801566532829 
Stand 81601566532829 
Stand 81701566532829 
Stand 81701566532830 
Stand 81601566532830 
Stand 81501566532830 
Stand 81701566532832 
Stand 81601566532832 
Stand 8201566532833 
Stand 8601566532834 
Stand 8901566532834 
Stand 81301566532836 


There are several object_name's, each with their own set of sensors. What I want is to calculate the interval of time for which the load_present flag is on per each object_name. I don't know what the timestamps are relative to, but they are fortunately in seconds. However, I can't wrap my head around the context that filters return and all my research has only confused me further. I resorted to modifying existing formulas that users have proposed for calculating time between employees clocking in and out (

My version is below, and it is returning vastly wrong numbers:

(object_history_20190824_000002 is the name of the csv I'm testing this with, will eventually scale up to SQL DB)




Run Increments = 
VAR a =
        MAX ( object_history_20190824_000002[timestamp] ),
        FILTER ( ALL ( object_history_20190824_000002 ),
            object_history_20190824_000002[ifm_object_id] = 1 && object_history_20190824_000002[ifm_name] = EARLIER(object_history_20190824_000002[ifm_name])
            && object_history_20190824_000002[timestamp] < EARLIER(object_history_20190824_000002[timestamp])
            && object_history_20190824_000002[load_present] <> EARLIER(object_history_20190824_000002[load_present])
            && object_history_20190824_000002[load_present] = 1

RETURN ( (object_history_20190824_000002[timestamp] - a) )




The table is default sorted by timestamp, and the next step would be to create a measure that sums the intervals in calculated column. If this can all be done in one measure, that would be amazing! Also, please feel free to educate me on my mistakes. I'd love to know why this fails.



Maybe my video here will help you (power query instead of DAX).

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post


Maybe my video here will help you (power query instead of DAX).

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I have thought about pursuing this avenue you've suggested, but I'm new to Power Bi and cautious against the performance impact of adding new columns. Most of the introductory videos/articles I've found encourage minimizing the table dimensions. Is there a large effect on Power BI's speed/performance in doing this?

Also, I have seen people get measures and calculated columns doing exactly what I want to do ( The only difference is that my equivalent IN/OUT flag is a 1/0 and persists for thousands of rows (as opposed to just one row). If no one has a solution in that regard, I guess I'll go with self-joining.

That is a very wise consideration. Believe me, I have been doing this for six years, and training people in power BI during that entire period. You won't find a bigger critic of adding new columns than me.

However there are times when it is better to use a column than it is to use a measure, and my view is that this is one of those times. Power BI is excellent at doing aggregations over very large columns, but it is not good at doing comparisons between two consecutive rows in a table. I suggest you use my video to bring the timestamps you need into another column using PQ.  If possible you should then subtract the timestamps from each other to create a duration column (in seconds or minutes, or what ever you need). Then remove the second timestamp column again. I.e., it is better to have an additional duration column that is to have another timestamp column loaded. 

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors