How to aggregate table based on moving sum

[Anonymous]

Hi,

I have a table with daily entries in this format for multiple years:

Location              Product                  Quantity                      Date

Minneapolis        Apples                    200                             3/22/2020

Minneapolis        Oranges                 234                             3/22/2020

Duluth                 Apples                   145                              3/22/2020

Duluth                 Oranges                 118                             3/22/2020

Minneapolis        Apples                    104                             3/23/2020

...

I want to:

1. Calculate which period of 28 consecutive days yielded the maximum quantity for each Location x Product pairing (ie get a moving sum for every 28 day period specific to that location and product, and then find the maximum value of it for each pairing).

2. Run/get additional statistics on dates in the identified max period for each Location x Product (ie average quantity, median quantity, start date of period, etc.)

Example output:

Location           Product                  Max Period Start Date                  Max Period Average Daily Quantity

Minneapolis     Apples                    1/14/2019                                     155.3

Minneapolis     Oranges                  2/3/2018                                       130.1

...

I've been able to create a measure that gives me a moving sum for each Location x Product x Day, but I've been unable to translate that back into getting only the rows associated with the max yield period for each location and product. I think if I could get the moving sum as a calculated column I could probably summarize my table based on that, but I'm stuck with it as a measure for now.

I also have pseudo data I can share if that would be helpful

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

Please check the below picture if that is what you are looking for.

The link to the sample pbix file is down below, and all measures are in the file.

https://www.dropbox.com/s/t2ypgefpqu2otdi/dancrist.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Yes. This helps a lot, thank you!

I think 'Start Date' is selecting the date preceding the max period instead of the first date in the period, but that's a simple fix if I add 1 to the return (ie 

enddates - nperiods + 1). I think I should be able to sort the rest of this out, thanks.