Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

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.

IncidentOrder DateDelivery Date
Order19/12/20189/13/2018
Order29/13/2018 
Order39/18/2018 
Order49/19/2018 
Order59/20/2018 
Order69/21/2018 
Order79/24/201810/21/2018
Order810/5/201810/31/2018
Order910/6/201810/11/2018
Order1010/8/201811/12/2018
Order1110/9/201810/11/2018
Order1210/9/201810/11/2018
Order1310/10/201810/11/2018
Order1410/10/201811/6/2018
Order1510/11/201810/11/2018
Order1610/12/201811/4/2018
Order1710/16/201811/14/2018
Order1810/17/201811/4/2018
Order1910/22/201811/4/2018
Order2010/24/201810/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. 

 

MonthOrderedDelivered
Sep-1871
Oct-18138
Nov-18 6
Total2015

Please help.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

11 REPLIES 11

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors