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

  • Naverie's avatar
    Naverie
    Helper I
    4 years ago

    Hi,

     

    Thank you for this. I haven't had chance yet to try it due to other commitments so can't let you know whether this has worked or not. But will when I've tried it out.

     

    thanks 

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      4 years ago

      Hi  Naverie ,

       

      Waiting for your update.

       

      Best Regards,
      Kelly

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