Calculated() with Var and literal filter

[mejiaks]

Hi

I am struggling with a measure that need to show a sum of orders based on a filter

when i specify the filter value with a literal like this

Orders 2018 with Literal = CALCULATE(sum(orders[qty]),FILTER(orders,orders[year]=2018))

the result show as spected

but when i specify the filter base on a value selected from a table, like this

Orders 2018 with Measure = CALCULATE(sum(orders[qty]),FILTER(orders,orders[year]=[Year # 1]))

being [Year # 1] a measure 

Year # 1 = IF ( HASONEVALUE ( 'Year # 1'[Year] ), VALUES( 'Year # 1'[Year]), 2018)

it does not work

see image below

i cannot work with literals cause I need to compare two years selected by the user and calculate difference between the two measures 

any ideas will be appreciatted

I would upload the file but i don't see the option

[v-juanli-msft]

Hi @mejiaks 

As tested, Zubair_Muhammad's suggestion should be helpful.

If it doesn't solve your requirement as said below.

"compare two years selected by the user and calculate difference between the two measures "

you could take my advice as below.

create a new table

Table2 = VALUES(Sheet7[year])

don't create relationships among three tables.

create measures

Year_1 = IF ( HASONEVALUE ( Table1[year] ), VALUES( Table1[year]), 2018)

Year_2 = IF ( HASONEVALUE ( Table2[year] ), VALUES( Table2[year]), 2018)

Measure1 = 
VAR myyear = [Year_1]
RETURN
    CALCULATE ( SUM (Sheet7[qty] ), FILTER ( ALL(Sheet7), Sheet7[year] = myyear ) )

Measure2 = 
VAR myyear = [Year_2]
RETURN
    CALCULATE ( SUM (Sheet7[qty] ), FILTER ( ALL(Sheet7), Sheet7[year] = myyear ) )

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zubair_Muhammad · Accepted solution]

@mejiaks 

Try this one...We need to take measure out of FILTER function

Orders 2018 with Measure =
VAR myyear = [Year # 1]
RETURN
    CALCULATE ( SUM ( orders[qty] ), FILTER ( orders, orders[year] = myyear ) )