cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Super User
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
ribisht17
Memorable Member
Memorable Member

@rsbin 

 

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]
 
 
 
ribisht17_0-1651604403371.png

 

 

NEW OUTPUT

 

ribisht17_0-1651729010995.png

 

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 

View solution in original post

5 REPLIES 5
ribisht17
Memorable Member
Memorable Member

@rsbin 

 

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

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

@ribisht17 ,

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,

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

ribisht17
Memorable Member
Memorable Member

@rsbin 

 

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]
 
 
 
ribisht17_0-1651604403371.png

 

 

NEW OUTPUT

 

ribisht17_0-1651729010995.png

 

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 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

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