Forum Discussion

RahulPBI's avatar
RahulPBI
Helper I
6 years ago
Solved

Calculated Column With If Then Show Null

Hi Experts

I have a calculated column that calculates the number of days between two dates and excludes any holidays. Managed to do that with help from members on this forumn. Its been working perfectly till I found another requirement where I need to add an IF clause to it

Below is the formula that works

 

1 Late = CALCULATE(SUM('Date Dimention'[IsWorkday]),
2 DATESBETWEEN('Date Dimention'[FullDateAlternateKey],
3 RF_DIFOT_Report[Promised Ship Date v1],
4 RF_DIFOT_Report[Actual Ship Date]-1))
 
I have another conditional column where I have calculated if the row is "On Time" or "Late" depending on certain criteria. Table and Coulmn Name is RF_DIFOT_Report[DIFOT vs Promise]
 
What I need to do is add a criteria to the current formula that if RF_DIFOT_Report[DIFOT vs Promise] = "On Time" then the the result in the Calculated Column Late must show as blank
 
See below
Thanks
 
Regards
RPBI
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    So, like this?

     

    Late = 
  IF(
    [DiFOT vs Promise] = "On Time",
    BLANK(),
    CALCULATE(
      SUM('Date Dimention'[IsWorkday]),
      DATESBETWEEN(
        'Date Dimention'[FullDateAlternateKey],
        RF_DIFOT_Report[Promised Ship Date v1], 
        RF_DIFOT_Report[Actual Ship Date]-1
      )
    )
  )

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    So, like this?

     

    Late = 
  IF(
    [DiFOT vs Promise] = "On Time",
    BLANK(),
    CALCULATE(
      SUM('Date Dimention'[IsWorkday]),
      DATESBETWEEN(
        'Date Dimention'[FullDateAlternateKey],
        RF_DIFOT_Report[Promised Ship Date v1], 
        RF_DIFOT_Report[Actual Ship Date]-1
      )
    )
  )