Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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!
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
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.
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
Is there a way to:
That would likely solve the problem if possible (I think)
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |