Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
philadams
Frequent Visitor

Value of orders shipping this period, created last working day

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 OrderOrder DateEstimated Ship DateValue
114/11/2221/11/22

10000

215/11/2215/12/2215000

 

Dates

DatesIs Current PeriodIs Last Working Day
14/11/22TRUETRUE
.........
15/12/22FALSE

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.

2 ACCEPTED SOLUTIONS
daXtreme
Solution Sage
Solution Sage

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]
    )
)

 

View solution in original post

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.

View solution in original post

4 REPLIES 4
philadams
Frequent Visitor

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).

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.

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

daXtreme
Solution Sage
Solution Sage

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]
    )
)

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.