Storing hourly data

[Naverie]

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!

[Naverie]

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 

[mwegener]

Hi @Naverie 

have you taken a look at the streaming dataset/dataflow yet?

https://docs.microsoft.com/en-us/power-bi/connect-data/service-real-time-streaming?WT.mc_id=DP-MVP-5...

https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-streaming?WT.mc_id=DP-...

[Naverie]

@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]

Hi @Naverie ,

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

[Naverie]

@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

DateTime	Quantity	Action	Calculation	Result
14/10/2021 12:00:00	0	Production order starts
14/10/2021 12:10:00	80	Item outputted	80/10	8 ppm	PPM up to this point
14/10/2021 12:30:00	80	Item outputted	160/30	5.33 ppm	PPM up to this point
14/10/2021 12:40:00	80	Item outputted	240/40	6.85ppm	PPM up to this point
14/10/2021 12:41:00	0	Production order ended	240/41	5.85ppm	Overall 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 · Accepted solution]

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]

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]

Hi  @Naverie ,

Waiting for your update.

Best Regards,
Kelly

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