Calculate Sum ordered by a specific date

[fabiAr89]

Hello

I have a table where I have the orders that have been made with two specific collumn dates: order placement and order delivery date.

My sales people are always asking me "How much of X product do we have in order to be delivered by the end of this month?" meaning that I have to sum all of kg for a specific order based on the month ending for the specific delivery date.

I have my data table connected to a Calendar table through the Creation date as my info table contains orders and deliveries and I would prefer not to split the tables, if possible.

When doing the Calculate Sum for Month to Date it's taking me the creaton date instead of the delivery date

Is there a possible way to do Calculate Sum EOM for the Delivery Date without splitting the table into orders and deliveries?

Thank you! 

[Anonymous · Accepted solution]

Hi, @fabiAr89 

Based on your information, I create sample table:

Then create new measure,  calculate the total number of products for all orders with delivery dates up to and including the end of the month, try the following DAX:

TotalKgToBeDeliveredByEOM = 
CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        'Table',
        'Table'[DeliveryDate] <= EOMONTH(TODAY(), 0)
    )
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rohit1991]

hi @fabiAr89 ,
Use this DAX measure to sum orders based on Delivery Date (not Order Creation Date):
TotalOrders_EOM =
CALCULATE(
SUM(Orders[Kg]),
FILTER(ALL(Orders), Orders[Delivery Date] <= EOMONTH(TODAY(), 0))
)

For a selected month from your Calendar table:
TotalOrders_SelectedEOM =
VAR SelectedMonth = MAX(Calendar[Date])
RETURN
CALCULATE(
SUM(Orders[Kg]),
FILTER(ALL(Orders), Orders[Delivery Date] <= EOMONTH(SelectedMonth, 0))
)

Use TotalOrders_EOM for current month orders. Also, use TotalOrders_SelectedEOM with a date slicer for dynamic selection.

[danextian]

Hi @fabiAr89 

Consider using an inactive relationship, which can be activated with the USERELATIONSHIP function. This is beneficial when there's already a relationship between your calendar table and the order date in the orders table, but you need another one for the shipping or delivery date. If there's an existing relationship between these tables, any additional relationships will become inactive since only one can be active at a time. However, this might not be exactly what you need.

Please provide a workable sample data (not an image), your expected result from the same sample data and the reasoning behind. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...