Forum Discussion

fabiAr89's avatar
fabiAr89
New Member
1 year ago
Solved

Calculate Sum ordered by a specific date

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 pro...
General Comment
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
rohit1991
Super User
1 year ago

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.