Create Custom Column using an IF Statement based on two columns

[Craig_01]

Hi All,

I have a dataset, that contains numerous columns of data, but main ones are:

Columns:

Branch

Status

Despatch Date

Requested Date

Promised Date

I want to calculate the difference in days between the Despatch Date & the advised date.. So the logic needs to be like below.

If Branch="York" & Status = "Out" (so i need both these to be satasified before it determins the true/false.

If True =  Despatch Date.Days - Requested Date.Days

If False = Despatch Date.Days - Promised Date.Days

Any assistance would be apprecaited.

[bhanu_gautam · Accepted solution]

@Craig_01 , Try using below formula for new calculated column

Difference = 
IF('Table'[Branch] = "York" && 'Table'[Status] = "Out",
    DATEDIFF('Table'[Despatch Date], 'Table'[Requested Date], DAY),
    DATEDIFF('Table'[Despatch Date], 'Table'[Promised Date], DAY)
)

[Anonymous · Accepted solution]

Thanks for the reply from @bhanu_gautam , please allow me to provide another insight: 
Hi  @Craig_01 ,

Here are the steps you can follow：

1. Add Column – Custom Column.

2. Enter code.

if [Branch]="Your" and [Status] ="Out"
then Duration.Days([Despatch Date] - [Requested Date])
else Duration.Days([Despatch Date] - [Promised Date])

3. 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

[Craig_01]

@Anonymous how would i exclude weekends in this formulation? @bhanu_gautam 

Thanks

[Craig_01]

Thanks, how to i exclude any Errors, where there is no data to calculate becasue of potentially columns that are only caputring data from 2024, and not in 2023 for example.

[bhanu_gautam · Accepted solution]

@Craig_01 , Try using below formula for new calculated column

Difference = 
IF('Table'[Branch] = "York" && 'Table'[Status] = "Out",
    DATEDIFF('Table'[Despatch Date], 'Table'[Requested Date], DAY),
    DATEDIFF('Table'[Despatch Date], 'Table'[Promised Date], DAY)
)