Forum Discussion
Calculating date/time differences
I have the following data in an SQL table. There are numerous Station ID's and each station ID has numerous scans per day. I would like to be able to get the first and last scan of each day and calculate the hours between those 2 times to obtain the length of time a machine was running. I know how to get this information at the day level. The problem I'm having is if I look at a week or a month or a years time in a visual it is factoring in all the downtime, basically just taking the first scan at the start of the week or month and the end scan and calculating the difference which factors in down time. Is there a way to sum date parts or something else that can bypass this issue?
Hi Dane
There are a few ways to achieve this. Ultimately, the below will get you the following output:
I've broken it down into the following steps just to allow you to follow:
1. Calculated column to return the Max DateTime by Station ID:
_MaxDateTimebyStationID =
VAR _StationID = 'Table'[Station ID]
VAR _MaxDateTime = MAXX ( FILTER ( ALL ( 'Table' ) , 'Table'[Station ID] = _StationID ) , 'Table'[DateTime] )
RETURN
_MaxDateTime2. Calculated column to return the Min DateTime by Station ID:
_MinDateTimebyStationID =
VAR _StationID = 'Table'[Station ID]
VAR _MinDateTime = MINX ( FILTER ( ALL ( 'Table' ) , 'Table'[Station ID] = _StationID ) , 'Table'[DateTime] )
RETURN
_MinDateTime3. Calculated column to return the Variance Duration in HH:MM:SS:
_TimeVariance(HHMMSS) = 'Table'[_MaxDateTimebyStationID] - 'Table'[_MinDateTimebyStationID]
In the tabular view, you will see the output as:
When you add a table in the canvas view, you can simply add the Station ID and drag the _TimeVariance(HHMMSS) column and get the following:
I've also attached the PBIX file to assist further 🙂
Hope this helps!
Theo 🙂
4 Replies
Hi Dane
May I ask if your problem has been solved? Did TheoC's post above help you? If any of the posts are helpful, then please consider Accepting it as a Solution to help other members find it faster. Many thanks!
If this doesn't work for you, would you mind providing some data that does not contain private information?
It makes it easier to give you a solution.
- Sample (dummy dataset) data as text, use the table tool in the editing bar
- Expected output from sample data
- Explanation in words of how to get from 1. to 2.
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
I have the same question and the solution provided seems to only look at the min and max date so it still includes downtime. I use a slicer to change the timeframe from a day to 20 days to 380 days (any amount of days) and need the working hours for each specific day and then sum those all up.
Hi Dane
There are a few ways to achieve this. Ultimately, the below will get you the following output:
I've broken it down into the following steps just to allow you to follow:
1. Calculated column to return the Max DateTime by Station ID:
_MaxDateTimebyStationID =
VAR _StationID = 'Table'[Station ID]
VAR _MaxDateTime = MAXX ( FILTER ( ALL ( 'Table' ) , 'Table'[Station ID] = _StationID ) , 'Table'[DateTime] )
RETURN
_MaxDateTime2. Calculated column to return the Min DateTime by Station ID:
_MinDateTimebyStationID =
VAR _StationID = 'Table'[Station ID]
VAR _MinDateTime = MINX ( FILTER ( ALL ( 'Table' ) , 'Table'[Station ID] = _StationID ) , 'Table'[DateTime] )
RETURN
_MinDateTime3. Calculated column to return the Variance Duration in HH:MM:SS:
_TimeVariance(HHMMSS) = 'Table'[_MaxDateTimebyStationID] - 'Table'[_MinDateTimebyStationID]
In the tabular view, you will see the output as:
When you add a table in the canvas view, you can simply add the Station ID and drag the _TimeVariance(HHMMSS) column and get the following:
I've also attached the PBIX file to assist further 🙂
Hope this helps!
Theo 🙂
Hello,
Help needed!
I have pretty much the same question and the above solution is not working for me. I have multiple lines over multiple days with multiple data points throughout each day. Below is a sample but the real one has thousands of data points....
Simply speaking - I need to calculate Working hours per day
I currently have my data filtered to "this day" and just take the datediff of min and max date for each line.
However, I need to be able to change the date range so that working hours is calculated by finding the first time and last time on a line each day and doing that for each day within the date range.
Any help would be awesome!! thanks!!
Line Date 1
2022-09-20 7:30 1
2022-09-20 15:301
2022-09-19 8:301
2022-09-19 16:302 2022-09-20 7:30 2
2022-09-20 15:302
2022-09-19 8:302
2022-09-19 16:303 2022-09-20 6:30 3
2022-09-20 15:303
2022-09-19 6:303
2022-09-19 16:30
