Forum Discussion

rsbin's avatar
rsbin
Community Champion
4 years ago
Solved

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...
Need Help
  • ribisht17's avatar
    ribisht17
    4 years ago

    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]
     
     
     

     

     

    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 

rsbin's avatar
rsbin
Community Champion
4 years ago

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's avatar
ribisht17
Super User
4 years ago

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