Calculating number of weeks since product release

[SuperMike]

Hello everybody!

I've imported two tables in power pivot.

One showing a product list and relative info such as product release date, units etc

The other one showing product sales per date.


The question is, how can i get to count the week number each sale was made since product release (with release date starting week 1)? Either by adding an extra calculating column or in an extra pivot table calculation. Either would help.

Thanks in advance.

Mike

 

[Jacob Barnett]

Exactly how you do it will depend on how you want to use it as there are different things that can be done with calculated columns measures.

If you wanted to simply assign each sale an 'age' then a calculated column in the table with the sales by date is probably the way to go. Working on the basis that your tables are called 'Product' and 'Sales' respectively and that the two are related by a common field called 'Product' you could go for something like:

=ROUNDUP(('Sales'[Date] - RELATED('Product'[ReleaseDate]) / 7 ),0)

The fundamental here is the RELATED which pulls the release date from the product table and subtracts it from the sale date to give a number of days since launch. It then divides by 7 to give weeks and the ROUNDDOWN should give round numbers in the way specified.

Jacob

 

[SuperMike]

Jacob, this was more than helpful!

The only small modification i made is to add a '+1' after the division by 7 so as to count first week as 1 and not 0.

Thank you very much for your help!!