Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |