Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ()
)
)
Hi, @Anonymous
According to your requirement. I think you can create a calculated column first because TREATAS function can't use expression. Then create a column to display results.
You can try:
Dateselect =
IF (
[PlannedGIDate] <= [OrderLineDeliveryDate],
[OrderLineDeliveryDate],
[PlannedGIDate]
)
result=
CALCULATE (
[Ordered Volume],
TREATAS ( VALUES ( 'Calendar'[FiscalDate] ), CustomerOrders[Dateselect] ),
CustomerOrders[OrderItemDeleted] <> TRUE ()
)
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Hi @Anonymous
can you please share a sample pbi file?
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |