Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Craig_01
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
bhanu_gautam
Super User
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!





View solution in original post

v-yangliu-msft
Community Support
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.

vyangliumsft_0-1718956444283.png

2. Enter code.

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

vyangliumsft_1-1718956444284.png

3. Result:

vyangliumsft_2-1718956487570.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
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.

vyangliumsft_0-1718956444283.png

2. Enter code.

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

vyangliumsft_1-1718956444284.png

3. Result:

vyangliumsft_2-1718956487570.png

 

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

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

 

Thanks

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
Super User
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!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.