Create Non Numeric (String) measure using IF and ELSE condition

[Anonymous]

Hi,

I have a table called Transactions and it has a column called Year. I need to create a "YearFlag" measure as,

if the Year is the current year then display "CY" and the Year is a previous year then "CY-1" and the year is a previous year minus 1 then "CY-2" and so on.

I'm able to achieve this as a calculated column using the below logic.

=SWITCH('Transactions'[Year],
YEAR(NOW()),"CY",
YEAR(NOW())-1,"CY-1",
YEAR(NOW())-2,"CY-2",
YEAR(NOW())-3,"CY-3",
YEAR(NOW())-4,"CY-4",
YEAR(NOW())-5,"CY-5",
YEAR(NOW())-6,"CY-6",
YEAR(NOW())-7,"CY-7",
YEAR(NOW())-8,"CY-8",
YEAR(NOW())-9,"CY-9",
YEAR(NOW())-10,"CY-10",
" ")

But I need to create the same logic as a measure. Can someone please help me with the same?

Thanks.

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could refer to below formula:

Measure = SWITCH(MAX('Transactions'[Year]),
YEAR(NOW()),"CY",
YEAR(NOW())-1,"CY-1",
YEAR(NOW())-2,"CY-2",
YEAR(NOW())-3,"CY-3",
YEAR(NOW())-4,"CY-4",
YEAR(NOW())-5,"CY-5",
YEAR(NOW())-6,"CY-6",
YEAR(NOW())-7,"CY-7",
YEAR(NOW())-8,"CY-8",
YEAR(NOW())-9,"CY-9",
YEAR(NOW())-10,"CY-10",
" ")

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Thanks @v-danhe-msft. This is perfectly fine and working. 

I have a follow-up question. Can you help me with that as well?

[v-danhe-msft]

Hi @Anonymous,

I would suggest you open a new thread in this forum.

Regards,

Daniel He

[Anonymous]

Sure @v-danhe-msft.

Thanks,

Akhil