Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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

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

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

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
Super User
Super User

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.