Count New and Missing Entries compared to previous week

[joshua1990]

I have a transactional table that contains all Items/ Products, that were sold in the corresponding week.

The table has a structure like this:

Date	Product	Value
08.02.2021	AA1	100
08.02.2021	AA2	105
08.02.2021	AA3	100
15.02.2021	AA1	100
15.02.2021	AA4	100

This table is linked with our calendar table and our Department Dimensional table.

The entries are made once every week.

Now I would like to determine 3 results:

1. How many different/ unique Products were sold per week
2. How many new products were sold compared to the previous week
3. How many old products (no entry) were sold compared to the previous week.

How would you calculate this?

In the end, I would like to use a Matrix with a Department Level as a row to show these results.

[stevedep]

Hi,

Please see below for a potential solution to your question. A measure is calculated to sum the sales in the period (week or month) before. You can then filter our records with of without sales in the previous period.

Working code available here. 

Hope it helps.

[jameszhang0805]

Hi joshua1990：

         Your request is similar to the calculation of New Customer Old customer, and Return Customer. So the first thing you need to clearly define what is the New production ？
1. the week of the current filter contents = the week of the first sales week in your whole fact table 
2. OR  the week of the current filter contents = the week of the launch date week in your product dimension table
If the definition is 1, SQLBI.com DAX Pattern has the article to explain how to calculate. 

[lbendlin]

Yes, but these would be dynamic table variables that you can create directly in DAX and then throw away again when you return the results.  That way you can compare any time period to any other time period in a flexible way.  I wouldn't be worried about peformance for that. Of course you'll have to test it and see if you get acceptable performance out of it.

[lbendlin]

you stuff the transactions for the two dates you want to compare into separate table variables and the use EXCEPT() to find the difference.

Let's say table A are the products sold on Feb 8 and table B the products sold on Feb 15.

EXCEPT (B,A) answers question 2, EXCEPT(A,B) answers question 3.

[joshua1990]

Thanks. Is there any other way?

[lbendlin]

There are always multiple ways to do that. What do you not like about my proposal?

[joshua1990]

@lbendlin Thank you so much for your support! I am just wondering - from a Dax Performance Point of View - if this is a good approach If I use to review the last 3 years on a weekly basis. Please correct me if I am wrong, but EXCEPT compared tables. That would mean that I have to create a table for each week, right?