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.
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?
Solved! Go to Solution.
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
_MaxDateTime
2. 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
_MinDateTime
3. 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 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
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:30 |
1 | 2022-09-19 8:30 |
1 | 2022-09-19 16:30 |
2 | 2022-09-20 7:30 |
2 | 2022-09-20 15:30 |
2 | 2022-09-19 8:30 |
2 | 2022-09-19 16:30 |
3 | 2022-09-20 6:30 |
3 | 2022-09-20 15:30 |
3 | 2022-09-19 6:30 |
3 | 2022-09-19 16:30 |
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.
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Gao
Community Support Team
If 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
_MaxDateTime
2. 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
_MinDateTime
3. 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 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |