Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
All,
I am needing to get a count of shipment status's over time.
My SHIP table has two dates I need to consider, Demand Date (Date it should ship) and Ship Date (actual date it did ship).
I created a DC Demand Date table to use specifically for this problem. It is a simple date table.
I need to be able to count on any given day the number and status of the shipments.
My problem is comparing one date (DC Demand Date[DC Demand Date]) to two dates (SHIP[Demand Date], SHIP[Ship Date]).
All of my attempts have been in DAX measures so far. I am trying to avoid Power Query as the Ship table is very big and I have already backed up all of the transformations to SQL Server to improve performance.
I have tried combinations of relating, not relating, etc.
Table | Header | Demand Date | Ship Date |
SHIP | 1 | 3/12/2021 | 3/15/2021 |
SHIP | 1 | 3/12/2021 | 3/15/2021 |
SHIP | 2 | 3/15/2021 | 3/20/2021 |
SHIP | 3 | 3/15/2021 | 3/15/2021 |
SHIP | 4 | 3/15/2021 | 3/15/2021 |
SHIP | 4 | 3/15/2021 | 3/15/2021 |
SHIP | 5 | 3/15/2021 | 3/15/2021 |
SHIP | 6 | 3/15/2021 |
Needed result:
Table | DC Demand Date | Carryover | Current | Carryover Ship | Current Ship |
DC Demand Date | 3/12/2021 | 0 | 1 | 0 | 0 |
DC Demand Date | 3/13/2021 | 1 | 0 | 0 | 0 |
DC Demand Date | 3/14/2021 | 1 | 0 | 0 | 0 |
DC Demand Date | 3/15/2021 | 1 | 5 | 1 | 3 |
DC Demand Date | 3/16/2021 | 2 | 0 | 0 | 0 |
DC Demand Date | 3/17/2021 | 2 | 0 | 0 | 0 |
DC Demand Date | 3/18/2021 | 2 | 0 | 0 | 0 |
DC Demand Date | 3/19/2021 | 2 | 0 | 0 | 0 |
DC Demand Date | 3/20/2021 | 2 | 0 | 1 | 0 |
DC Demand Date | 3/21/2021 | 1 | 0 | 0 | 0 |
Solved! Go to Solution.
Hi, @mtyoung99
Thank you very much for your feedback.
Please check the below picture.
I created two tables, which are date-table and ship-table, and they are connected via TWO-Inactive-Relationship.
Please also check the link down below. All measures are in the pbix file.
I hope this helps.
https://www.dropbox.com/s/5fnfpt2cn8wtgwj/mtyoung99.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @mtyoung99
I only could calculate shipments count after demand & before ship.
I think this is what you meant by Carryover. Other than Carryover, I could not understand.
The below is the measure that I wrote for "Shipments count after demand & before ship".
In this case, I had no relationship ( or inactive relationship) between the two tables. If you have one active relationship, you can add ALL or Allselect function into the measure to inactivate the relationship.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hoping this helps explain what I am after.
I am wanting to keep track of how any orders drop to a DC (on the day they should Ship), if they ship, if not then they would be carried over to the next day as carryover until they do ship.
Hi, @mtyoung99
Thank you very much for your feedback.
Please check the below picture.
I created two tables, which are date-table and ship-table, and they are connected via TWO-Inactive-Relationship.
Please also check the link down below. All measures are in the pbix file.
I hope this helps.
https://www.dropbox.com/s/5fnfpt2cn8wtgwj/mtyoung99.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Jihwan,
That is exactly what I am after. Brilliant!!
Thank you sir!!
⭐⭐⭐⭐⭐
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |