How to aggregate table based on moving sum

Question

Hi,&nbsp;I have a table with daily entries in this format for multiple years:&nbsp;Location&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Quantity&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateMinneapolis&nbsp; &nbsp; &nbsp; &nbsp; Apples&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/22/2020Minneapolis&nbsp; &nbsp; &nbsp; &nbsp; Oranges&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/22/2020Duluth&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Apples&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;145&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3/22/2020Duluth&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Oranges&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;118&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/22/2020Minneapolis&nbsp; &nbsp; &nbsp; &nbsp; Apples&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 104&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/23/2020...&nbsp;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.)&nbsp;Example output:&nbsp;Location&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Max Period Start Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Max Period Average Daily QuantityMinneapolis&nbsp; &nbsp; &nbsp;Apples&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/14/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;155.3Minneapolis&nbsp; &nbsp; &nbsp;Oranges&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2/3/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;130.1...&nbsp;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.&nbsp;I also have pseudo data I can share if that would be helpful

jihwan_kim · Accepted Answer

Hi,&nbsp;Anonymous&nbsp;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.&nbsp;&nbsp;https://www.dropbox.com/s/t2ypgefpqu2otdi/dancrist.pbix?dl=0&nbsp;&nbsp;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.

Forum Discussion

How to aggregate table based on moving sum

2 Replies

Related content

Using Aggregate Tables and Partitions

Manage aggregations multiple fact tables DirectLake over OneLake

moving average help

Aggregation in Matrix Table

Data Aggregation issue