Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Reporting constant errors:variant data-type cannot be used to define calculated columns

Hi,
I have a table named: FACT_Stock, I want to create a calculated column to define remaining shelf life (months).
Mine methodology is to use Column [EXP], [PictureDate], these two colum type is date, [EXP] is mixed with some blanks.
Formula of Remaining shelf life (months) is written as 

[RSL] =
VAR Months = IF(FACT_Stock[EXP] = BLANK(), BLANK(), DATEDIFF(FACT_Stock[PictureDate],FACT_Stock[EXP],MONTH))
RETURN
IF(
    Months = BLANK(),
    "Undefined",
    SWITCH(
        TRUE(),
        Months <= 0,
        "Expired",
        Months >= 48,
        "Not relevant",
        Months
    )
)

It keeps reporting errors: Expressions that yield variant data-type cannot be used to define calculated columns. I tried serveral ways to fix the error, such as use CONVERT formula to transform data type, but the error doesn't diappear

Need Help

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    Hi Anonymous 

     

    Plese supply a representataive sample of your data (or all of it), so we can see what your working with.  Really hard to debug something without seeing the data.

     

    Regards

     

    Phil

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Philip,
      Table is like this way

      ItemCodeEXPSOHPictureDate
      H0844900012021/11/14702024/3/31
      H0845600012022/5/14602024/3/31
      H0845600012022/3/14132024/3/31
      H0845600012022/5/14402024/3/31
      H0845600012999/12/31102024/3/31
      H0845800012022/4/14602024/3/31
      H0845800012022/6/14142024/3/31
      H0845800012022/6/1462024/3/31
      H0845800012023/4/18502024/3/31
      H0845800012022/6/14152024/3/31
      CBLM250 192024/3/31
      CBLM250 362024/3/31
      CBLM250T 12024/3/31
      CBLM250T 72024/3/31
      505512026/12/204392024/3/31
      498312026/7/1838292024/3/31
      498312026/7/1852024/3/31
      505512026/6/21157222024/3/31
      498312026/3/3112024/3/31
      509412026/3/304962024/3/31
      CL20811T 342024/3/31
      CL20811T 7672024/3/31
      CL20811TT 92024/3/31
      H2820300012099/1/1122024/4/1
      H2820500012099/1/1542024/4/1
      H2850100012099/1/1152024/4/1
      H3280100012099/1/198752024/4/1