Forum Discussion
Counts based on multiple dates in a table
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
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?
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
7 Replies
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
@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.
Put it on a Dropbox, google drive or OneDrive and put the link into a post.
Hi,
Share the link from where i can download your PBI file.
