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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate Function Based on a Date Difference

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

 

2 REPLIES 2
v-janeyg-msft
Community Support
Community Support

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

tamerj1
Super User
Super User

Hi @Anonymous 

can you please share a sample pbi file?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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