Count number of weeks product is sold, based on daily sales

Question

How to calculate number of weeks that the product is sold (per year), based on daily sales.

There is a daily sales table (millions of entries)

BookingDate	ProductId	Pieces
01-01-2023	1	10
01-02-2023	1	2

Product Table

ProductId	ProductName	Price
1	Kiwi gold	0.99

I'd like to know how many weeks each product has been active (sold at least once in a week) in a year

Year	Product	WeeksActive
2023	Kiwi gold	10
2023	Kiwi green	12
2022	Kiwi gold	50

This number is then needed to make further calculations. Is this possible?

(tried: extracted WeekNumber and Year from Booking date, tried creating a new table that holds articles per week per year, but don't know enough to build it)

Answer 1

I assume you want a DAX solution, but you tagged it for powerquery, so what the heck. Add columns to pull out the month and week. Remove all columns but the two columns and the ProductID. Remove all duplicate rows. Group on year and product ID

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"BookingDate", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([BookingDate])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Week", each Date.WeekOfYear([BookingDate])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"ProductId", "Year", "Week"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"ProductId", "Year"}, {{"Weeks", each Table.RowCount(_), Int64.Type}})
in  #"Grouped Rows"

then merge in the name