Counts based on multiple dates in a table

[prekapRev]

I have a table with multiple date fields (Order Date, Ship Date, Delivery Date). I need to report on counts of sales, shipment and delivery for each month where either sale, shipment or delivery was made. 

Here is an example of mockup source data

Sales_Order_Num	Product	Order_Date	Ship_Date	Delivery_Date
1	P1	8/19/2020	8/29/2020	9/3/2020
2	P1	8/24/2020	9/3/2020	9/13/2020
3	P1	9/3/2020	9/8/2020	9/19/2020

I need to display counts like this

Month	Order Count	Shipment Count	Delivery Count
August	2	1	0
September	1	2	3

I have a date table and created one active (on Order date) and 2 indirect (ship and delivery date) relations.

I created measures for shipment and Delivery counts using USERELATIONSHIP like below

Ship_Count = CALCULATE(count(Sales[Sales_Order_No]), USERELATIONSHIP(Cal_Date[Date], Sales[Ship_Date]))

Delivery_Count = CALCULATE(count(Sales[Sales_Order_No]), USERELATIONSHIP(Cal_Date[Date], Sales[Delivery_Date]))

But I am still getting result like below (shipment and Delivery counts are same as order count)

Month is from Calendar table

Month	Order Count	Shipment Count	Delivery Count
August	2	2	2
September	1	1	1

How do I get the shipment and delivery counts displayed correctly?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[dedelman_clng]

Hi @prekapRev  - I cannot reproduce your results. I get your expected output when I put your code in (although the DAX says "Sales Order No", while the data says "Sales Order Num", but I assume that was just an error in typing).

Order Count = CALCULATE(COUNT(Sales[Sales_Order_Num]))

Ship_Count = CALCULATE(count(Sales[Sales_Order_Num]), 
               USERELATIONSHIP(DateTab[Date], Sales[Ship_Date]))

Delivery_Count = CALCULATE(count(Sales[Sales_Order_Num]), 
               USERELATIONSHIP(DateTab[Date], Sales[Delivery_Date]))

Perhaps you left something out of the explanation that makes the situation more complicated?

David

[prekapRev]

@dedelman_clng I want to attach my pbix file but I don't know how to attach any files here. I have done it exactly like you are showing in your solution but my result is different. not sure why.

[dedelman_clng]

Put it on a Dropbox, google drive or OneDrive and put the link into a post. 

[prekapRev]

Here is the link to my report

https://drive.google.com/file/d/1CTl7i0qiUvqYu0I-PuQPKkld19XVXxlm/view?usp=sharing

[dedelman_clng · Accepted solution]

Hi @prekapRev  -

Force your relationships between Cal_Date and Sales to be 1-to-many (Cal_Date on the 1 side) and it should work for you.

Hope this helps

David

[prekapRev]

Changing cardinality fixed the problem. Thanks! I wonder though that why should that be the problem 🤔