Forum Discussion
Filtering Data on Order Date but Display Values based on Invoice Date in a Matrix
Hi everyone - looking for some help with filtering and displaying data with multiple dates.
I recieve daily order data that includes 3 important pieces of information:
- Order Date - date the order was recieved
- Order Amount - $ amount
- Invoice Date - the date the product is planned to ship
Each day we look at not only the amount of orders that came in but we also want to know which month they are booked to ship. I am struggling to filter the information on the order date, then display it in a matrix based on the invoice date.
If I could figure this out it would allow me for instance to look at all orders that came in during March and which month they are planned to ship.
Any suggestions are appreciated since I am spinning my wheels on how to work with the multiple dates.
Thank you,
Brad Meissner
3 Replies
I appreciate the help. Here is a screenshot with my data removed from it:
The table on the left will show orders by order date. IE all the orders that came in during a given time frame. The table on the right then will show when those same orders will ship/invoice.
Here is an example of what my data looks like:
Customer Order Amount Order Date Invoice Date Bob 10 3/21/2023 4/3/2023 Cindy 20 3/21/2023 5/6/2023 Jeff 40 3/20/2023 6/1/2023 I can get the table on the left to work, but the table on the right I am struggling to get to show the same orders that I am slicing for by order date to then matrix out based on their invoice date(month).
Any help is appreciated.
Brad
You can do this with two date tables. One tied to the order date and the other to invoice date. This is my preference but it does duplicate date tables. The other approach is to relate both dates to the date table. Then create measure that activates the inactive relationship with USERELATIONSHIP.
