Can't compare value of type integer

[lazzarjovvch74]

Hi,

I created the next measure but got an error saying that doesn't support comparing values of type integer with values of type Text.

Measure 1 = IF(HASONEVALUE('MyTable'[Year]), CALCULATE(AVERAGE('MyTable'Price]), 'MyTable'[Year] = FORMAT(VALUES('MyTable'[Year])-1,BLANK())))

Just to emphasize that column Year is of type INTEGER. If I convert the column 'Year' to type Text, the above formula works, but I want to keep it as integer. Any idea how I can rewrite this formula?

Thanks

[TomMartens]

Hey,

I'm wondering why you not just use this

... 'MyTable'[Year] = 'MyTable'[Year]-1

Regards,
Tom

[lazzarjovvch74]

Hi,

I tried that but an error appeared (red line under 'MyTable'[Year]😞

[TomMartens · Accepted solution]

Silly me,

you have to rewrite your measure this way

Measure 1 =  
IF(HASONEVALUE('MyTable'[Year]),
    CALCULATE(
        AVERAGE('MyTable'[Price])
        ,FILTER(
            ALL('MyTable'[Year])
            ,'MyTable'[Year] = MAX('MyTable'[Year])-1
        )
    )
    ,BLANK()
) 

I'm sorry for my confusion.

Regards,
Tom

[lazzarjovvch74]

Tom, absolutely amazing. It works. Please could you explain why did you use 'MAX' function in the FILTER portion of the expression?

Best!

[TomMartens]

Hey,

I'm glad it works!

Regarding the usage of MAX, please allow me to direct your attention to this blog article I once wrote: https://www.minceddata.info/2018/02/21/the-weird-year-to-date-calculation/

Regards,
Tom