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