Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |