Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Hi @DonTwan ,
You will have to first create a Calculated Column that gives the Week Number of Year, then use it to get the Active Weeks using a Calculated Measure. I have created some dummy data to demonstrate this
Calculated Column
Calculated Measure
Below is the screenshot
Regards,
Hi @Thejeswar
thanks for the reply.
Using this method, the numbers don't seem to count in the matrix
Using a table, i've duplicated the first row
but when display it in the table, it doesn't actually sum
Hi @DonTwan ,
I matrix also this is working fine.
But I see in your table you are having WeekNo as a Column. A Measure is always for the context of the rows. i.e. If you are using a measure, it will be dynamic at row level based on the other Columns in the table.
Here when you are including WeekNo in the column, for each row of WeekNo the Active Week is 1. That is why you are seeing 1 in every row.. If you see in your question, you never mentioned you are going to split the data by weekno. Active week is always 1 for each week..
If you remove WeekNo from the Column, the WeeksActive will work as expected.
You can share the expected output with some sample dummy data. That might help give a more precise way to achieve this
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |