I have a calculate function that calculates Ordered Volume using a treatas function below that works as intended. I am trying to use that same calculation with an adjusted ship date but am unsure on the syntax or correct methodology. Essentially, I am trying to replace the CustomerOrders[ShipmentDate] in the treatas function with an adjusted ship date. The adjusted ship date is simply the difference between the OrderLineDeliveryDate and the PlannedGI Date in the variable DATEDIFFERENCE. If the PlannedGIDate is 1/25/2022 and the OrderLineDeliveryDate is 1/27/2022, the new date which should be used in the calculate function is 1/27/2022 unless the PlannedGIDate is after the OrderLineDeliveryDate in which case PlannedGIDate should be used.
The DATEDIFFERENCE variable does not work correctly; I've tried using MIN/MAX wrappers in the DATEDIFF function or including an IF() statement but neither works so I've included the base formula that hopefully shows what I'm trying to achieve. Essentially, my goal is to create an adjusted ship date that then works in the CALCULATE function below. Due to the data model, I cannot create a calculated column or do anything involving power query so creating a DAX formula is my only option.
var DATEDIFFERENCE = SUMX(CustomerOrders, DATEDIFF(CustomerOrders[OrderLineDeliveryDate], CustomerOrders[PlannedGIDate], DAY))
var ADJSHIPDATE = SUMX(CustomerOrders, CustomerOrders[ShipmentDate] - DATEDIFFERENCE)
RETURN
CALCULATE (
[Ordered Volume],
TREATAS ( VALUES ( 'Calendar'[FiscalDate] ), CustomerOrders[ShipmentDate] ,
CustomerOrders[OrderItemDeleted] <> TRUE ()
)
)
