# Maximum Consumption for Material "Y" over Any 30-Day Period

1. ## Maximum Consumption for Material "Y" over Any 30-Day Period

Hello Experts,

This is my first of (I'm sure of) many posts. My question today is, How do I calculate the max for a specific entity over a set window of time?

In this case, I would like to calculate the maximum consumption/usage of a specific material over any 30-day window inside of the year. The first two points can be taken care of with a MAXIFS, but I don't understand how to have excel evaluate every 30-day instance for each material. Seems doable, I think I am just lacking the knowledge. Any advice would be appreciated!

A small sheet with sample data should be attached.

Best,
Plenty o' Questions

2. ## Re: Maximum Consumption for Material "Y" over Any 30-Day Period

Since you have 365, you have Power Query available to you. I have created a parameter query coupled with a max grouping for each product for the thirty day period selected.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for a video which demonstrates how to use Power Query code provided.

3. ## Re: Maximum Consumption for Material "Y" over Any 30-Day Period

Maybe this:

To get the maximum consumption of any 30 day period, enter this in a cell:

=MAX(SUMIFS(\$C\$2:\$C\$13,\$B\$2:\$B\$13,">="&B2:B13,\$B\$2:\$B\$13,"<="&B2:B13+30,\$A\$2:\$A\$13,F2))

To get the date that the 30 day period starts, try this:

=LET(
a,SUMIFS(\$C\$2:\$C\$13,\$B\$2:\$B\$13,">="&B2:B13,\$B\$2:\$B\$13,"<="&B2:B13+30,\$A\$2:\$A\$13,F2),
b,MAX(a),
c,MATCH(b,a,0),
INDEX(B2:B13,c))

See attached.

4. ## Re: Maximum Consumption for Material "Y" over Any 30-Day Period

That worked perfect, plus I learned something. You certainly don't get to say those in conjunction too often! Much appreciation, above and beyond Gregb11

5. ## Re: Maximum Consumption for Material "Y" over Any 30-Day Period

Thank you for taking your time to look at this. I think I need to brush up on my PowerQuery.

6. ## Re: Maximum Consumption for Material "Y" over Any 30-Day Period

You're welcome, and thanks for the rep and feedback.

7. ## Re: Maximum Consumption for Material "Y" over Any 30-Day Period

Essentially made my Tuesday (albeit maybe it wasn't the world's best Tuesday), but that is the very least I could do.

There are currently 1 users browsing this thread. (0 members and 1 guests)