Forum Discussion
Post ProdigyCOUNTROWS this week vs last week with specific key
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?
3 Replies
- daxer-almighty
Solution Sage
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.
joshua1990Sry, 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)
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 🙂
