Forum Discussion
Monthly Orders received vs orders delivered
Hi All,
I have some data like below. I want to show a monthly comparison of number of Orders received vs orders delivered in a clustered column chart.
| Incident | Order Date | Delivery Date |
| Order1 | 9/12/2018 | 9/13/2018 |
| Order2 | 9/13/2018 | |
| Order3 | 9/18/2018 | |
| Order4 | 9/19/2018 | |
| Order5 | 9/20/2018 | |
| Order6 | 9/21/2018 | |
| Order7 | 9/24/2018 | 10/21/2018 |
| Order8 | 10/5/2018 | 10/31/2018 |
| Order9 | 10/6/2018 | 10/11/2018 |
| Order10 | 10/8/2018 | 11/12/2018 |
| Order11 | 10/9/2018 | 10/11/2018 |
| Order12 | 10/9/2018 | 10/11/2018 |
| Order13 | 10/10/2018 | 10/11/2018 |
| Order14 | 10/10/2018 | 11/6/2018 |
| Order15 | 10/11/2018 | 10/11/2018 |
| Order16 | 10/12/2018 | 11/4/2018 |
| Order17 | 10/16/2018 | 11/14/2018 |
| Order18 | 10/17/2018 | 11/4/2018 |
| Order19 | 10/22/2018 | 11/4/2018 |
| Order20 | 10/24/2018 | 10/25/2018 |
My problem is which date column should i choose to create the month column to show the correct data.
I am looking for something like below which i can then represent in a clustered column chart however. At this moment if i create month from either of the dates then the data will be incorrect. I am already using the date table and using the month column from date table itself.
| Month | Ordered | Delivered |
| Sep-18 | 7 | 1 |
| Oct-18 | 13 | 8 |
| Nov-18 | 6 | |
| Total | 20 | 15 |
Please help.
To accomplish this, you'll want to make sure that you have a date dimension table that you can link both columns to. One of them will be inactive because you can only have 1 active relationship between 2 tables, and that's ok.
You can make use of the USERELATIONSHIP function to use the inactive relationship for the measure to sum the item that's related to that date.
For example, lets say you connect both dates to the date dimension table and you want the order date to be the active relationship and the Delivery date to be the inactive date. You would then write 2 different measures for Ordered and Delivered.Ordered = COUNTX(table, table[Incident]) Delivered = CALCULATE ( COUNT( table[Incident]), USERELATIONSHIP(calendar[date], table[Delivery Date]))
Then you can create a table visual with the date from the date dimension table as the axis and the 2 measures you just created as the values.
11 Replies
To accomplish this, you'll want to make sure that you have a date dimension table that you can link both columns to. One of them will be inactive because you can only have 1 active relationship between 2 tables, and that's ok.
You can make use of the USERELATIONSHIP function to use the inactive relationship for the measure to sum the item that's related to that date.
For example, lets say you connect both dates to the date dimension table and you want the order date to be the active relationship and the Delivery date to be the inactive date. You would then write 2 different measures for Ordered and Delivered.Ordered = COUNTX(table, table[Incident]) Delivered = CALCULATE ( COUNT( table[Incident]), USERELATIONSHIP(calendar[date], table[Delivery Date]))
Then you can create a table visual with the date from the date dimension table as the axis and the 2 measures you just created as the values.Create date table which is disconnected with main table.
Order_placed = CALCULATE(COUNT(Test[Order Date]),FILTER(Test,CONTAINS('Calendar table','Calendar table'[Date],Test[Order Date])))order deliverded = CALCULATE(COUNT(Test[Delivery Date]),FILTER(Test,CONTAINS('Calendar table','Calendar table'[Date],Test[Delivery Date])))Drag year month from calendar table and above two measures in table visual.Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.