Forum Discussion
Calculate Machine Duration
Good Day All,
Having difficulties getting a Measure to work properly.
Scenario as follows: I am trying to measure how many hours each day each machine is running.
The dataset includes multiple records for multiple machines for each day.
| DeviceID | Date | DateTime | Duration (sec) |
| HSCP-2149 | 5/3/2022 | 5/3/2022 0:47 | 5925060 |
| HSCP-2149 | 5/2/2022 | 5/2/2022 22:44 | 5907960 |
| HSCP-2149 | 5/2/2022 | 5/2/2022 21:44 | 5904360 |
| HSCP-2149 | 5/2/2022 | 5/2/2022 21:04 | 5902020 |
| HSCP-2149 | 5/2/2022 | 5/2/2022 20:46 | 5900940 |
| HSCP-2149 | 5/2/2022 | 5/2/2022 20:43 | 5900760 |
| HSCP-2149 | 5/2/2022 | 5/2/2022 20:26 | 5899680 |
| HSCP-2149 | 5/2/2022 | 5/2/2022 0:43 | 5897160 |
In essence, I need to grab the first value of each day. I have tried the following:
CALCULATE( FIRSTNONBLANK( MyTable[ObdEngineSeconds], SUM(MyTable[ObdEngineSeconds] )))
This works fine when I capture each record individually. When I try to create a Measure with Variables, I am unable to get it to work properly. I do have a Calendar Table connected by Date to this fact table.
My Measure needs to capture the first record of the current day and subtract it from the first record of the previous day.
In the above example the result is: 27,900 seconds / 3600 = 7.75 hours.
Any help or guidance from the Community would be much appreciated.
Much thanks and Regards,
I was able to do it for multiple DEVICE IDs, hope it helps! CHECK THE BOLD ONE
Step 0
Min DateTime Within a Day = CALCULATE(MIN(Secs[DateTime]),FILTER(ALL(Secs),Secs[Date]=max(Secs[Date] )))NEWStep 0Min DateTime Within a Day Secs2 = CALCULATE(MIN('Secs (2)'[DateTime]),FILTER(ALL('Secs (2)'),'Secs (2)'[Date]=max('Secs (2)'[Date])),FILTER(ALL('Secs (2)'),'Secs (2)'[DeviceID]=MAX('Secs (2)'[DeviceID]))Step 1)Maxx Date = MAXX( all(Secs),Secs[Date])NEWStep1:Maxx Date Secs2 = CALCULATE( max('Secs (2)'[Date]),FILTER(all('Secs (2)'),'Secs (2)'[DeviceID]=max('Secs (2)'[DeviceID])))Step 2/////Overall Max DateTimeMaxx DateTime = MAXX( all(Secs),Secs[DateTime])NEWStep2Maxx DateTime Secs2 = CALCULATE( max('Secs (2)'[DateTime]),FILTER(all('Secs (2)'),'Secs (2)'[DeviceID]=max('Secs (2)'[DeviceID])))Step 3
Current Day Secs = CALCULATE(MAX(Secs[Duration (sec)]),FILTER(all(Secs),Secs[Min DateTime Within a Day]=Secs[DateTime]),FILTER(all(Secs),[Maxx Date] < Secs[DateTime]))NEWStep3Current Day Secs2 = CALCULATE(MAX('Secs (2)'[Duration (sec)]),FILTER(all('Secs (2)'),[Min DateTime Within a Day Secs2]='Secs (2)'[DateTime]),FILTER(all('Secs (2)'), DAY('Secs (2)'[Date])=DAY([Maxx Date Secs2]) ),FILTER(ALL('Secs (2)'),'Secs (2)'[DeviceID]=MAX('Secs (2)'[DeviceID])))Step 4Prev Day Secs New = CALCULATE(MAX(Secs[Duration (sec)]),FILTER(all(Secs),Secs[Min DateTime Within a Day]=Secs[DateTime]),FILTER(all(Secs),[Maxx DateTime]> Secs[DateTime]))NEW
Step4
Prev Day Secs New secs2 = CALCULATE(MAX('Secs (2)'[Duration (sec)]),FILTER(all('Secs (2)'),[Min DateTime Within a Day Secs2]='Secs (2)'[DateTime]),FILTER(all('Secs (2)'), DAY('Secs (2)'[Date])=DAY([Maxx Date Secs2])-1 ),FILTER(ALL('Secs (2)'),'Secs (2)'[DeviceID]=MAX('Secs (2)'[DeviceID])))Step 5 Last:
Final Diff = Customer[Current Day Secs] - [Prev Day Secs New]NEWStep 5Final Diff2 = [Current Day Secs2] - [Prev Day Secs New secs2]NEW OUTPUT
Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users
5 Replies
I was able to do it for multiple DEVICE IDs, hope it helps! CHECK THE BOLD ONE
Step 0
Min DateTime Within a Day = CALCULATE(MIN(Secs[DateTime]),FILTER(ALL(Secs),Secs[Date]=max(Secs[Date] )))NEWStep 0Min DateTime Within a Day Secs2 = CALCULATE(MIN('Secs (2)'[DateTime]),FILTER(ALL('Secs (2)'),'Secs (2)'[Date]=max('Secs (2)'[Date])),FILTER(ALL('Secs (2)'),'Secs (2)'[DeviceID]=MAX('Secs (2)'[DeviceID]))Step 1)Maxx Date = MAXX( all(Secs),Secs[Date])NEWStep1:Maxx Date Secs2 = CALCULATE( max('Secs (2)'[Date]),FILTER(all('Secs (2)'),'Secs (2)'[DeviceID]=max('Secs (2)'[DeviceID])))Step 2/////Overall Max DateTimeMaxx DateTime = MAXX( all(Secs),Secs[DateTime])NEWStep2Maxx DateTime Secs2 = CALCULATE( max('Secs (2)'[DateTime]),FILTER(all('Secs (2)'),'Secs (2)'[DeviceID]=max('Secs (2)'[DeviceID])))Step 3
Current Day Secs = CALCULATE(MAX(Secs[Duration (sec)]),FILTER(all(Secs),Secs[Min DateTime Within a Day]=Secs[DateTime]),FILTER(all(Secs),[Maxx Date] < Secs[DateTime]))NEWStep3Current Day Secs2 = CALCULATE(MAX('Secs (2)'[Duration (sec)]),FILTER(all('Secs (2)'),[Min DateTime Within a Day Secs2]='Secs (2)'[DateTime]),FILTER(all('Secs (2)'), DAY('Secs (2)'[Date])=DAY([Maxx Date Secs2]) ),FILTER(ALL('Secs (2)'),'Secs (2)'[DeviceID]=MAX('Secs (2)'[DeviceID])))Step 4Prev Day Secs New = CALCULATE(MAX(Secs[Duration (sec)]),FILTER(all(Secs),Secs[Min DateTime Within a Day]=Secs[DateTime]),FILTER(all(Secs),[Maxx DateTime]> Secs[DateTime]))NEW
Step4
Prev Day Secs New secs2 = CALCULATE(MAX('Secs (2)'[Duration (sec)]),FILTER(all('Secs (2)'),[Min DateTime Within a Day Secs2]='Secs (2)'[DateTime]),FILTER(all('Secs (2)'), DAY('Secs (2)'[Date])=DAY([Maxx Date Secs2])-1 ),FILTER(ALL('Secs (2)'),'Secs (2)'[DeviceID]=MAX('Secs (2)'[DeviceID])))Step 5 Last:
Final Diff = Customer[Current Day Secs] - [Prev Day Secs New]NEWStep 5Final Diff2 = [Current Day Secs2] - [Prev Day Secs New secs2]NEW OUTPUT
Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Usersrsbin Hope it helped, appreciate your response here so that we can close the thread
Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau UsersGood Morning,
Your solution did help some, but I haven't been able to complete the puzzle as yet.
Because I am dealing with multiple machines ( over 100 ) at this point, I need another filter in there for Device.
I haven't been able to determine why, but I haven't been able to fully replicate your solution.
Would like to keep the thread open until I can get to a proper solution.
Thanks for your input, as I keep working on this.
Kind Regards,
Sure, I get what you say, that should also work (multiple DEVICE ID), as you say need to add another filter, I will also check meanwhile, we need to group it with Device ID as well but I think you will be able to crack 🙂
Regards,
Ritesh
