Monthly Orders received vs orders delivered

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

@Anonymous, such solutions are way, way, way slower than solutions based on relationships. What you suggest is not recommended. Virtual relationships should be the last resort when there's no other way to achieve what one wants.

Best
D

[Anonymous]

@Anonymous  you are right i tried this solution and this is not working because i am only able to create a bi directional filtering for 1 out of 2  data tables, with other one i am getting ambiguity error hence Powerbi doesnt allow me to do it.

Moreover i am quite surprised that its not possible because its quite a common thing and i was able to do in Tableau all fine.

[Anonymous]

Hi there.

First, I want to say that the correct solution to the problem has already been given in this thread. It's the one based on an INACTIVE relationship from a Date table to one of the date columns. This has always worked OK and it will now if properly understood and implemented.

But to do it properly one has to 1) know how to handle inactive relationships and 2) how to create a proper Date table.

By the way, there's no need to have bi-directional filtering in this case at all. What's more, bi-directional filtering is (almost) never the thing to do. It should be used only when there's no other way to achieve what one wants with good standard techniques and when all the consequences of this technique are well understood by the modeler. Good advice is to stay away from bi-directional filtering unless you're experienced and know all the pitfalls.

Please implement the solution based on active/inactive relationships from a Date table to the fact table.

Best

D

[Anonymous]

@Anonymous,
Thanks for your input.
So what's the best solution for such scenarios.

Thanks
Pravin

[Anonymous · Accepted 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.

[Anonymous]

Hi,

I am already using a date dimention table and using the month column in the visual created from that table itself still however the numbers arent correct. This seems quite tricky however i am sure its quite common scenario too.

[Anonymous]

Did those DAX measures work? @Anonymous 

[Anonymous]

@Anonymous  that worked, however for some reason when i split it by a "Monthyear" field i created from 'date' field from Date table and then make sure that the numbers are correct i compare the numbers in another table but this time by 'MonthYear' field created from a date field from the data table (imagine this is our "delivery date') the monthly split of numbers do not match. See the screnshot below. 
Consider the month on the right table as 'delivery date' derived from the data table and and left is monthyear derived from date field of the date table.MonthYear on left is from date table, on the right the monthyear field is from the data table.

[Anonymous]

Hi,

No they didnt , and i have also updated the post with some more info for others.

[Anonymous]

See this sample for a solution that appears to accomplish what you're looking for. Also, note that your sample set of data don't share the same years in Order Date vs Delivery Date.

sample file