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!