Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |