COUNTROWS this week vs last week with specific key

[joshua1990]

Hello everyone!

I have those 5 tables linked:

• A simple calendar table (Week, month etc is not shown here but they exists)
• An attribute list: Attribute per article
• A sales master: 1 unique row for each Order
• A booking table: All bookings per order (several rows per order)
• An archive that shows me the open orders per week.

What I would like now to calculate is this:

• The number of orders in the archive table that does not exist in this week compared to last week > Lost orders
• The number of orders in this week, that are listed in the table archive and have a booking = "XX" in the table bookings.

How would you do that?

[Anonymous]

hello @joshua1990 

if your archive table has every order number then i would create a distinct table using the order number from archive table.  in power query you could merge the sales and booked table to it and then you will see orders that have a $0 value for booked and or sales 

or you could create a calculated table of disitinct order numbers and then add columns for each table 

I would do it in power query and can show you how if you want to send some sample data over 🙂 

[daxer-almighty]

Hi again @joshua1990 

Your relationships are certainly not correct. Are you sure you want to have 1-to-1 in your model? I would strongly advise against such a design.

[joshua1990]

Sry, that true. This is just an issue with the sample data.

The relations are:

Calendar - Sales (One to Many)

Calendar - Archive (One to Many)

Bookings - Sales Master (Many to One)

Archive - Sales Master (Many to One)

Attribute - Sales Master (One to Many)