Dealing with ship date filters when some lines have not shipped

DAX DAX Calculations
1

I have a document number, Sales Order, that has five lines. 4 lines, each with a qty of 1, shipped on 1/1/2021, and has a ship date in the “Ship Date” column. 1 line, qty 1, did not ship and does not have a date in the “Ship Date” column.

I have calculated that they had a 80% fulfillment rate, since 4 qty shipped out of 5 qty. I want to filter the report down to just document numbers that have shipped and show the detail of the 5 lines but if I use the “Ship Date” column as a filter and filter it to 1/1/2021, the line that did not ship is filtered out but is needed in the analyzation.

How do I filter the report to the day a document number shipped but also include the lines on that document number that did not ship?

2

Hi @Usates,

Use a disconected date table to retrieve all ordersnumbers that contain shipped items for that date selection. With those Order IDs you can filter the table.

If you need further assistance please provide a sample PBIX
I hope this is helpful

1 Like
3

Sample.pbix (605.4 KB)

Thanks for the reply. Could you please show your example in this file? Thank you!!

4

Hi @Usates,

First created a disconnected Ship Date table and reduced the dates to only those that are a ship date. Replaced the date field in the slicer with this one.

ShipDate selecton (disconected) = 
INTERSECT(
    VALUES( DateDimension[Date] ),
    VALUES( Sales[Ship Date] )
)

.
image
.

Created a visual level filter with this measure

ShipDateFilter =
VAR ShipDates = VALUES( 'ShipDate selecton (disconected)'[Date] )
VAR Orders = SELECTCOLUMNS( FILTER( ALL( Sales ), Sales[Ship Date] IN ShipDates ), "@Doc", [Document Number] )
VAR NewFilter = INTERSECT( VALUES( Sales[Document Number] ), Orders )
RETURN

IF( NOT( ISEMPTY( NewFilter )),
    1
)

.

image
image1416×238 21.4 KB

.
Here’s your sample file. Sample (6).pbix (607.4 KB)
I hope this is helpful

2 Likes
5

How do I get the measures to update to match the detail?

image
image1096×383 42.4 KB

6

Hi @Usates,

For that you need to look into fixing totals

.

A pattern like this will do the job.

Total Qty Ordered Card = 
VAR vTable = FILTER( Sales, [ShipDateFilter] =1 )
RETURN

CALCULATE( SUM(Sales[Qty Ordered]),
    vTable
)
1 Like