CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktopm
Noviceatcoding
Noviceatcoding

Reputation: 1

Calculation of maximum of a running average over a span of time

I have been working with a dataset of a toys production company. They have been producing toys since 1990. They have production in multiple locations all around the globe. I have a sheet which has the following data - serial no. of the toy, type of the toy, revision of the type of the toy, location where it was produced, feeder line used, machine used, date of start of production of the toy and completion of production of the toy. Each toy has it's own serial number. There are around 20 different types of toys. Most of the toys has had revisions at some point of time. A feeder line comprises of 2 machines which share the same feeder input (One could use only one machine per feeder line or both). So, if I have two feeder lines say, FL001 and FL002, then my machines could be FL001-M1, FL001-M2, FL002-M1 and FL002-M2. Now, I have around 200,000 rows of data and I'm about to analyse them in PowerBi.

I would like to know what is the maximum number of toys I've produced in each machines historically over a 365 day period. Because the production can vary week-on-week, there is a high chance that for machine FL001-M1, the max. no. of toys over a 365 day period is 400 over May 2011-April 2012 and FL001-M2 is 230 in June 1995-May 1996.

What I've tried to do is to first calculate

toys produced per day = calculate(countrows('Database'))

Then I tried to calculate

Running average = calculate ([toys produced per day],
datesinperiod(
'Datebase'[Production Start Date],
lastdate('Datebase'[Production Start Date]),
-365,DAY))

Finally I calculated

365 day max. output per week = divide([Running average],52,0)

But I'm not sure if I'm calculating the maximum over the entire period of production in that particular machine or just the running average in the last 365 days of production in that machine.

Here is a model dataset.

Serial no. Toy type Toytype Revision Location Feederline Machine Production Start Date End Date
TS0010105 TS 05 US FL001 M1 19th August 1996 20th October 1996
TS0010206 TS 06 Eng FL001 M2 10th June 2005 01st July 2005
PD0080209 PD 09 Jap FL008 M2 13th April 2024 25th April 2024

Upvotes: 0

Views: 44

Answers (0)

Related Questions