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

[Mheadland]

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 to use other forum results to fix this such as: ensuring all column are the same data type (date), using a today column rather then the today() function, entering my filter as date() rather than just the date as dd/mm/yyyy, and a few others but nothing seems to be working.

Does anyone have any other suggestions?

Many thanks 🙂

[Ahmedx]

remove the quotes and use the functions FORMAT( TODAY(), "dd-MM-yyyy")

[Mheadland]

Apologies, I don't quite understand what you mean by this, is there any chance you could write it down for me so I can visualise what I need to add/remove. 

Thank you in advance

[Ahmedx]

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]

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]

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]

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 · Accepted solution]

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] ) & ""
        )
    )
)

[Mheadland]

This is the one 🙂 Thank you so much for all of your help!!

[Ahmedx]

always happy to help

[Ahmedx]

you need to add two quotes where the date is like this TODAY()&""
you have the date in two places

[Mheadland]

Thank you for that sugesstion, it has fixed the error issue but rather than completing the calculation it just shows me the sum ( as seen in the far right column). Do you know how to fix this?