Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

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:

1.PNG

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

 

Regards,

Daniel He

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

View solution in original post

4 REPLIES 4
v-danhe-msft
Employee
Employee

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:

1.PNG

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

 

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

Hi @Anonymous,

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Sure @v-danhe-msft.

 

Thanks,

Akhil

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.