cancel
Showing results for
Did you mean:  Super User

## 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,

1 ACCEPTED SOLUTION  Memorable Member

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] )))

NEW
Step 0
Min 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])

NEW
Step1:
Maxx Date Secs2 = CALCULATE( max('Secs (2)'[Date]),FILTER(all('Secs (2)'),'Secs (2)'[DeviceID]=max('Secs (2)'[DeviceID])))

Step 2
Maxx DateTime = MAXX( all(Secs),Secs[DateTime])
/////Overall Max DateTime

NEW
Step2
Maxx 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]))

NEW
Step3
Current 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 4
Prev 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]

NEW
Step 5
Final 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 5  Memorable Member

Did a lot of hard work to solve it, 🙂 Hope it helped you, if not please let me know, would not like to leave this mid-way.

Regards,

Ritesh  Memorable Member

@rsbin  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 Users  Super User

Good 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,  Memorable Member

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  Memorable Member

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] )))

NEW
Step 0
Min 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])

NEW
Step1:
Maxx Date Secs2 = CALCULATE( max('Secs (2)'[Date]),FILTER(all('Secs (2)'),'Secs (2)'[DeviceID]=max('Secs (2)'[DeviceID])))

Step 2
Maxx DateTime = MAXX( all(Secs),Secs[DateTime])
/////Overall Max DateTime

NEW
Step2
Maxx 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]))

NEW
Step3
Current 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 4
Prev 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]

NEW
Step 5
Final 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 Announcements The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform. #### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison! #### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022. Top Solution Authors
Top Kudoed Authors
Users online (1,646)