topic Re: Create Custom Column using an IF Statement based on two columns in DAX Commands and Tips

Create Custom Column using an IF Statement based on two columns

Craig_01 — Wed, 19 Jun 2024 09:22:13 GMT

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.

Re: Create Custom Column using an IF Statement based on two columns

bhanu_gautam — Wed, 19 Jun 2024 10:46:49 GMT

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

Re: Create Custom Column using an IF Statement based on two columns

Anonymous — Fri, 21 Jun 2024 07:55:05 GMT

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

Re: Create Custom Column using an IF Statement based on two columns

Craig_01 — Fri, 21 Jun 2024 10:42:10 GMT

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.

Re: Create Custom Column using an IF Statement based on two columns

Craig_01 — Mon, 24 Jun 2024 19:06:11 GMT

Anonymous how would i exclude weekends in this formulation? bhanu_gautam

Thanks