Storing hourly data

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!