topic Re: Count New and Missing Entries compared to previous week in DAX Commands and Tips

Count New and Missing Entries compared to previous week

joshua1990 — Tue, 16 Feb 2021 17:39:10 GMT

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:

How many different/ unique Products were sold per week
How many new products were sold compared to the previous week
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.

Re: Count New and Missing Entries compared to previous week

lbendlin — Wed, 17 Feb 2021 03:32:19 GMT

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.

Re: Count New and Missing Entries compared to previous week

joshua1990 — Wed, 17 Feb 2021 05:20:03 GMT

Thanks. Is there any other way?

Re: Count New and Missing Entries compared to previous week

lbendlin — Wed, 17 Feb 2021 12:31:12 GMT

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

Re: Count New and Missing Entries compared to previous week

joshua1990 — Wed, 17 Feb 2021 17:04:20 GMT

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?

Re: Count New and Missing Entries compared to previous week

lbendlin — Wed, 17 Feb 2021 17:25:21 GMT

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.

Re: Count New and Missing Entries compared to previous week

jameszhang0805 — Thu, 18 Feb 2021 07:43:34 GMT

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.

Re: Count New and Missing Entries compared to previous week

stevedep — Thu, 18 Feb 2021 13:23:27 GMT

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.