topic Re: Value of orders shipping this period, created last working day in DAX Commands and Tips

Value of orders shipping this period, created last working day

philadams — Wed, 16 Nov 2022 12:45:30 GMT

Hi, I have the following tables and am trying to calculate the value of orders created on the last working day that are shipping in the current period. Or, which is how i'm currently trying to calculate it, the value of orders shipping in this period that were created on the last working day.

Customer Orders

Customer Order	Order Date	Estimated Ship Date	Value
1	14/11/22	21/11/22	10000
2	15/11/22	15/12/22	15000

Dates

Dates	Is Current Period	Is Last Working Day
14/11/22	TRUE	TRUE
...	...	...
15/12/22	FALSE	FALSE

I have an active relationship between Order Date and the dates table, and an inactive one between Estimated Ship Date and the dates table. Building in steps i've created the following;

Total Ordered Value = SUM('Customer Orders'[Value])

Order Value by Ship Date = CALCULATE ( [Total Ordered Value], USERELATIONSHIP ( 'Customer Orders'[Estimated Ship Date], Dates[Date] ) )

Order Value Shipping This Period = CALCULATE ( [Order Value by Ship Date], FILTER ( Dates, Dates[Is Current Period] = TRUE ) )

These all work correctly, but where i'm stuck is creating the final measure that takes the last one above but filters it using the Order Date that has a related date record where 'Is Last Working Day' = TRUE. I've tried a few variations but I think i'm coming unstuck with how to implement the pattern that uses a different relationship on the final filter. It could be that i've started on the wrong foot so any pointers on this are appreciated.

Re: Value of orders shipping this period, created last working day

daXtreme — Wed, 16 Nov 2022 13:50:25 GMT

Might be this... but it's not entirely clear what you want. You should always be absolutely clear about what you want and give good examples, even if by hand. This is because you know what you want, we don't know anything about your model.

[Order Value Shipping This Period] = CALCULATE ( [Order Value by Ship Date], KEEPFILTERS( Dates[Last Working Day] ) )

Re: Value of orders shipping this period, created last working day

philadams — Wed, 16 Nov 2022 14:09:18 GMT

Apologies, I should have put a better example of what I was expecting to be returned. The date filters that i'm using in the date table (Is Last Working Day etc.) are recalculated each day so are always relative to todays date.

If todays date was 15/11/22 (is last working day would be true for the 14/11/22, current period would be true for all dates in todays period)

I want to return a value of 10000, as there was is an order on the last working day that ships in the current period

If todays date was 16/11/22 (is last working day would be true for the 15/11/22, current period would be true for all dates in todays period)

I want to return a value of 0, as there was an order placed on the last working day but it does not ship in the current period.

I'm assuming there is a pattern for this as the date filters could be almost anything, i.e. order value shipping next month, but created this month, for example.

EDIT: Fundamentally, what i'm trying to achieve is filtering a table by both an active relationship (order_date) and an inactive relationship (estimated_ship_date), using different fields from the same related table. Other ways i've though about it is creating a table with all orders created yesterday, then all orders with a ship date in this period and doing an intersect, but this feels more complex than it needs to (accepting that it might not be).

Re: Value of orders shipping this period, created last working day

daXtreme — Wed, 16 Nov 2022 14:14:20 GMT

The easiest way is to have 2 date tables, one that will join on one type of date (Ship Calendar), the other on the other (Order Calendar). This way it's dead easy to achieve what you want by selecting from them. Only thing is name the tables some sensible names and always remember which Calendar you're working with.

Re: Value of orders shipping this period, created last working day

philadams — Wed, 16 Nov 2022 14:21:08 GMT

Yeah, I do have that as an option and like you say, it's much more straightforward then.