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 hour...
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!

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

      mwegener sending a sample file will be an issue due to company policies etc. The best I can do for you is below, hopefully, it's ok.

       

      Production line 1

      DateTimeQuantityActionCalculationResult 
      14/10/2021 12:00:000Production order starts   
      14/10/2021 12:10:0080Item outputted80/108 ppmPPM up to this point

      14/10/2021 12:30:00

      		80Item outputted160/305.33 ppmPPM up to this point

      14/10/2021 12:40:00

      		80Item outputted240/406.85ppmPPM up to this point

      14/10/2021 12:41:00

      		0Production order ended240/415.85ppmOverall PPM for that production order.

       

      This would then be on a line chart for example to see where their performance dips with a KPI changing to the current PPM.

       

      The ideal scenario would be to have 15-minute increments along an axis in a chart and the PPM calculated on the fly, e.g.  order starts at 12:00, 80 items outputted at 12:20  so at 12:15 the PPM would be zero. Another lot of 80 items are outputted at 12:35 so at 12:30 the PPM would be 80/30 but at 12:45 the PPM would be 240/45 if another lot of items had been outputted.

       

      I'm not sure whether any of that makes sense but not sure how else to explain it. Might have to draw it out if not lol!

       

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        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!