Forum Discussion

Craig_01's avatar
Craig_01
Helper I
2 years ago
Solved

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.

 

 

AND
CUSTOMCOLUMN
DAX
if
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Craig_01
      Helper I
      2 years ago

      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.