Forum Discussion

Naverie's avatar
Naverie
Helper I
4 years ago
Solved

Storing hourly data

Hi all,

 

A request has been made to show hourly production data. The PPM is calculated as a whole for what has happened on the production order currently but I'm wanting to store this somehow hourly.

 

The data isn't stored this way as is a calculation that will change at the end of a production order e.g. 30 mins into the order PPM could be 20 as 600 packs have been produced but in another 30 mins it could now be 18 as 1080 have now been produced for that whole hour. 

 

What I'm tryinng to do is store the information in power bi so it can be used to trend performance.

 

Hopefully this makes sense!

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Naverie ,

     

    First go to query editor>split datetime column to date and time columns and add an index column;

    Create a column to get the minute:

     

    Minute = MINUTE('Table'[Time])

     

    Then create another column as below:

     

    Result =
VAR minindex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Action] = "Production order starts"
                && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
        )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Quantity] ),
        FILTER (
            'Table',
            'Table'[Index] >= minindex
                && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
        )
    )
RETURN
    IF (
        'Table'[Action] = "Production order starts",
        0,
        DIVIDE ( _sum, 'Table'[Minute] )
    )

     

    And you will see:

    If you wanna get the  ideal scenario,you need to first create a dim table as below:

     

    dim = GENERATE(GENERATESERIES(0,24,1),SELECTCOLUMNS(GENERATESERIES(0,60,15),"value2",[Value]))

     

    Then repeat the above calculation to get result2.

    An output is:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

8 Replies

    • Naverie's avatar
      Naverie
      Helper I
      4 years ago

      mwegener I think I've found a way round it as the time data is stored.

       

      Only thing is I sort of know what I need to do but not sure how to translate it into Power BI to work.

       

      So the outputs are stored with a timestamp.

       

      For the first output I would need to calculate the PPM from the start of the Production order to that time. It would then keep getting lower as the duration is longer but no more packs have been produced. When the next lot of packs are outputted it would then calculate the new PPM from the start of the order to this timestamp.

       

      Is any of that possible? I'm guessing it might be and just need some fancy DAX.

       

      Thanks in advance

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        4 years ago

        Hi Naverie ,

         

        I think if you provide an sample file and describe what your expected result is, we can give it a try.

         

  • Naverie's avatar
    Naverie
    Helper I
    4 years ago

    Hi @mwenger,

     

    No I haven't as I didn't know this existed.

     

    However, looking at the links you've sent they are probably beyond my skill set for Power BI and not sure whether we have the ability to do this as we are using on prem report server.

     

    Is there another solution available?

     

    Thanks