How to get Product Counts per 7 Days from this kind of table

[Covington]

Date	ProductID
12/17/2020	5
12/16/2020	9
12/15/2020	3
12/14/2020	5
12/13/2020	9
12/12/2020	1
12/11/2020	4
12/10/2020	1
12/9/2020	3
12/8/2020	2
12/7/2020	1
12/6/2020	3
12/5/2020	5
12/4/2020	9

Week	1	2	3	4	5	6	7	8	9
1	1	0	1	1	2	0	0	0	2
2	2	1	2	0	1	0	0	0	1

Essentially I want to count the number of times a product ran based upon a rolling 7-day span.  I've been playing around and can't seem to figure out where to go with this.  I'm sure it isn't difficult. 

In Excel I might do a CountIf, but then I would have to explicity call out the rows to aggregate.

Thanks in advance!

[Fowmy]

@Covington 

You can add a column to your table get the 7 days Weekly bracket. I assume the dates are in sequence and the granularity of your table is as shown in the sample. I took Thursday to Friday as the week. You can change it as you need though. 

Then, add a matrix as below. 

If you want to see zero for missing values, you will have to have a dimension table for Product ID or create a new table to populate zero using generate or crossjoin. It is possible to do it in PQ as well.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Jimmy801]

Hello @Covington 

you can transform the date to week, add a index column and then pivoting your product ID

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7RDcAgCATQVRq+TQRErbMY91/Dpu3RJvD54MLNSaJZelZWPt6hRJVWelbtXl00nCqoOBnoC5YYVJA4CcicOFyN8PCEKKSHVAupGlrar+Ta", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ProductID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ProductID", Int64.Type}}, "en-US"),
    #"Calculated Week of Year" = Table.TransformColumns(#"Changed Type",{{"Date", Date.WeekOfYear, Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Calculated Week of Year", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"ProductID", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"ProductID", type text}}, "de-DE")[ProductID]), "ProductID", "Index", List.Count)
in
    #"Pivoted Column"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Covington]

Very close, I'm playing around with it. This gives me weekly stuff based upon the calendar, but I'm looking for a "Rolling 7 days", so trying to figure out how to tweak this 🙂  Nice code!

[Jimmy801]

Hello @Covington 

what you mean by rolling 7 days? from now on - 7 days - 14 days etc.?

Then try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7RDcAgCATQVRq+TQRErbMY91/Dpu3RJvD54MLNSaJZelZWPt6hRJVWelbtXl00nCqoOBnoC5YYVJA4CcicOFyN8PCEKKSHVAupGlrar+Ta", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ProductID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ProductID", Int64.Type}}, "en-US"),
    #"Calculated Week of Year" = Table.TransformColumns(#"Changed Type",{{"Date", each Number.RoundUp(Duration.TotalDays(Date.From(DateTime.FixedLocalNow())- _)/7), Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Calculated Week of Year", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"ProductID", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"ProductID", type text}}, "de-DE")[ProductID]), "ProductID", "Index", List.Count)
in
    #"Pivoted Column"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Covington · Accepted solution]

I ended up creating another column in the Excel table that would read Week1, Week2, Week3, etc..   Then I pivoted off of that and it worked great.  Appreciate all of the help.

[v-yingjl]

Hi @Covington ,

Glad to hear the issue is solved. You can accept the suitable reply as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,
Community Support Team _ Yingjie Li

[Covington]

Is there a way to:

• Add a Column
• Iterate each 7 and set the value to Week{x}
• Pivot the table?

That would likely solve the problem if possible (I think)

[MattAllington]

If you don't have a calendar table, you should get one of those first. https://exceleratorbi.com.au/power-pivot-calendar-tables/

then you need to work out what you need. A rolling 7 day number is different to weekly numbers (which is what your example seems to be).  Assuming you want the totals by week like in your example, you simply need a week number in your calendar table. You can add that in power query. Select the date column, go to add column, date, day of week. 

[Covington]

@MattAllington I'm really fine converting the dates to a simple number to be honest.  I could number those 1 - 14 (based upon Date Desc) and it would be fine to me.  I'm looking at it similar to a Rolling Average, but this would be more of a Rolling Count, I guess.