Forum Discussion

Mheadland's avatar
Mheadland
Frequent Visitor
2 years ago
Solved

Expressions that yield variant data-type cannot be used to define calculated columns ( with dates)

Hi all,   I am currently trying to create a column that states how long an item has been in stock, this is the formula I am using but I keep recieving the same error message:   I have tried t...
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    or this

    DealerStockDays =
IF (
    AND (
        '00_StockData'[R date] = DATE ( 2173, 10, 13 ),
        '00_StockData'[WSDate] = DATE ( 2173, 10, 13 )
    ),
    "-",
    IF (
        '00_StockData'[R date] = DATE ( 2173, 10, 13 ),
        ( TODAY () - '00_StockData'[WSDate] ) & "",
        (
            ( VALUE ( '00_StockData'[R date] ) - '00_StockData'[WSDate] ) & ""
        )
    )
)
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

you copy the measure and paste it here.
I can't copy it from a picture.
after that I'll show you what I mean.

Mheadland's avatar
Mheadland
Frequent Visitor
2 years ago
DealerStockDays = IF(AND('00_StockData'[R date] = DATE(2173,10,13), '00_StockData'[WSDate] = DATE(2173,10,13)), "-", IF('00_StockData'[R date] = DATE(2173,10,13), TODAY()&- '00_StockData'[WSDate],(VALUE('00_StockData'[R date])& -'00_StockData'[WSDate])))
  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    pls try this

     

    DealerStockDays =
IF (
    AND (
        '00_StockData'[R date] = DATE ( 2173, 10, 13 ),
        '00_StockData'[WSDate] = DATE ( 2173, 10, 13 )
    ),
    "-",
    IF (
        '00_StockData'[R date] = DATE ( 2173, 10, 13 ),
        FORMAT(TODAY ()-'00_StockData'[WSDate],"dd-MM-yyyy"),
        FORMAT( ( VALUE ( '00_StockData'[R date] ) - '00_StockData'[WSDate] ),"dd-MM-yyyy")
    )
)

     

    • Ahmedx's avatar
      Ahmedx
      Icon for Super User rankSuper User
      2 years ago

      pls try this

      DealerStockDays =
IF (
    AND (
        '00_StockData'[R date] = DATE ( 2173, 10, 13 ),
        '00_StockData'[WSDate] = DATE ( 2173, 10, 13 )
    ),
    "-",
    IF (
        '00_StockData'[R date] = DATE ( 2173, 10, 13 ),
        FORMAT ( TODAY () - '00_StockData'[WSDate], "dd-MM-yyyy" ),
        FORMAT (
            ( VALUE ( '00_StockData'[R date] ) - '00_StockData'[WSDate] ),
            "dd-MM-yyyy"
        )
    )
)
      • Ahmedx's avatar
        Ahmedx
        Icon for Super User rankSuper User
        2 years ago

        or this

        DealerStockDays =
IF (
    AND (
        '00_StockData'[R date] = DATE ( 2173, 10, 13 ),
        '00_StockData'[WSDate] = DATE ( 2173, 10, 13 )
    ),
    "-",
    IF (
        '00_StockData'[R date] = DATE ( 2173, 10, 13 ),
        ( TODAY () - '00_StockData'[WSDate] ) & "",
        (
            ( VALUE ( '00_StockData'[R date] ) - '00_StockData'[WSDate] ) & ""
        )
    )
)