Distinct count of Shipment records over time

[mtyoung99]

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

[Jihwan_Kim]

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.

Shipments count after demand before ship =

CALCULATE (

COUNTROWS ( Ship ),

FILTER (

VALUES ( Ship[Demand Date] ),

Ship[Demand Date] <= MAX ( 'DC Demand Date'[DC Demand Date] )

),

FILTER (

VALUES ( Ship[Ship Date] ),

Ship[Ship Date] > MIN ( 'DC Demand Date'[DC Demand Date] )

)

)

+ CALCULATE (

COUNTROWS ( Ship ),

FILTER (

VALUES ( Ship[Demand Date] ),

Ship[Demand Date] <= MAX ( 'DC Demand Date'[DC Demand Date] )

),

FILTER ( VALUES ( Ship[Ship Date] ), Ship[Ship Date] = BLANK () )

)

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.

[mtyoung99]

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.

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

[mtyoung99]

Jihwan,

That is exactly what I am after. Brilliant!!

Thank you sir!!

⭐⭐⭐⭐⭐