How to calculate date

[tyan]

So I have such modeling. Four tables.

I need to create a measure/column : (Delivery Date - OC shipping date)= date to ship

My biggest question is if no show date in delivery date column then I will use actual delivery date, and do the same thing 

(actual Delivery Date- OC shipping date)

My priority is first use  DIMDELIVERY TABLE if empty then use DIMACTUALDELIVERY. 

How do i write this function ? 

[v-yangliu-msft · Accepted solution]

Hi  @tyan ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Actual Delivery date_measure =
CALCULATE(MAX('Dim Actual Delivery'[Actual Delivery date]),FILTER(ALL('Dim Actual Delivery'),'Dim Actual Delivery'[Group]=MAX('Fact Sales Table'[Group])))

Delivery date_measure =
CALCULATE(MAX('DimDelivery'[Delivery date]),FILTER(ALL('DimDelivery'),'DimDelivery'[Group]=MAX('Fact Sales Table'[Group])))

datediff =
IF(
   [Delivery date_measure]=BLANK(),
    DATEDIFF(
   MAX('Fact Sales Table'[OC Shipping date]),[Actual Delivery date_measure],DAY),
    DATEDIFF(
   MAX('Fact Sales Table'[OC Shipping date]),[Delivery date_measure],DAY))

2. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@tyan , if you have some common field like sales order id

then you try an approach like given in this blog

same in direct query

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...