Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DonTwan
Regular Visitor

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

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-2023110
01-02-202312

 

Product Table

 

 ProductId  ProductName  Price 
1Kiwi gold0.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 
2023Kiwi gold10
2023Kiwi green12
2022Kiwi gold50

 

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

3 REPLIES 3
Thejeswar
Resident Rockstar
Resident Rockstar

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

Week No = WEEKNUM('Table'[ BookingDate ])

 

Calculated Measure

WeeksActive = DISTINCTCOUNT('Table'[Week No])

Below is the screenshot

Thejeswar_0-1684774061265.png

 

Regards,

Hi @Thejeswar 

thanks for the reply. 

Using this method, the numbers don't seem to count in the matrix

 

DonTwan_0-1684780417125.png

 

Using a table, i've duplicated the first row

 

DonTwan_1-1684780681330.png

 

but when display it in the table, it doesn't actually sum

 

DonTwan_2-1684780719228.png

 

Hi @DonTwan ,

I matrix also this is working fine.

Thejeswar_0-1684816063956.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.