cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Create Custom Column using an IF Statement based on two columns

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.

2 ACCEPTED SOLUTIONS
Super User

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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Community Support

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

4 REPLIES 4
Community Support

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

Frequent Visitor

@v-yangliu-msft how would i exclude weekends in this formulation? @bhanu_gautam

Thanks

Frequent Visitor

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.

Super User

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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!