Problem in Set analysis expression

[#1]

Hi all,

I have a Data like

OrderDate	Region	Rep	Item	Sales  units	Unit Cost	Total Sales
1/6/14	East	Jones	Pencil	95	1.99	189.05
1/23/14	Central	Kivell	Binder	50	19.99	999.50
2/9/14	Central	Jardine	Pencil	36	4.99	179.64

I want to extract the sales of 2014 by using set analysis in gauge chart, i wrote the expression like as below

sum( {$<Year(OrderDate)={2014}>}[Total Sales])

but it didn't work. Please help me .

[Gysbert_Wassenaar · Accepted solution]

You can only use field names on the left side of a set modifier, not an expression like Year(OrderDate).

Try this: sum( {$<OrderDate={'=Year(OrderDate)=2014'}>}[Total Sales])

A better solution is to create a year field in the script, i.e. Year(OrderDate) as OrderYear, and use that in the expression: sum( {$<Year={2014}>}[Total Sales])

[Anonymous]

Thank you for marking the correct answer here. Gysbert always has good ideas and advice. I hope it's helpful for your progress.

You can also follow top contributors like Gysbert to see some of the other discussions and content they are creating.

Best Regards, Sara

[pratap6699]

make this date conversion in script level like Load Year(orderdate) as year resident of tablename; chart exp--> sum({<year={2014}>}sales)

[or]

chart exp-->Sum({<orderdate={'=Year(orderdate)=2014'}>}sales)