Dynamic Sorting Based on Current Month

[Powell360]

I have creatded a reference table in my .pbix so that I can order months in one of a few ways, as dictated by the context of the report. 

I have a table which contains  MonthName, CallendarSort, FYSort and DynamicSort.

Month name is a string, callendar sort has the numeric value based on the callendar (Jan-Dec), FYSort has the numerical value based on financial year (Apr-Mar) and I want to create DynamicSort based on the current month with current month = 12, previous month = 11 and so on.

I implemented a very rough nested "if" solution but it won't work for months still to come based on the standard calendar. Here is the script I have used to populate my custom column, can anyone let me know what changes I need to make please?

Thanks in advance

if([CallendarSort]=Date.Month(DateTime.LocalNow())) then(12) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-1) then(11) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-2) then(10) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-3) then(9) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-4) then(8) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-5) then(7) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-6) then(6) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-7) then(5) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-8) then(4) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-9) then(3) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-10) then(2) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-11) then(1) else(
0
)
)
)
)
)
)
)
)
)
)
)
)

[Powell360 · Accepted solution]

I was hoping to come up wit

if([CallendarSort]=Date.Month(DateTime.LocalNow())) then(12) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-1) then(11) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-2) then(10) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-3) then(9) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-4) then(8) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-5) then(7) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-6) then(6) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-7) then(5) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-8) then(4) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-9) then(3) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-10) then(2) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-11) then(1) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+1) then(1)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+2) then(2)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+3) then(3)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+4) then(4)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+5) then(5)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+6) then(6)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+7) then(7)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+8) then(8)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+9) then(9)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+10) then(10)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+11) then(11)else(
0)))))))))))))))))))))))

h a more elegant solution but I have just continued the logic of the 1st draft to look forwards as well

[Anonymous]

Thank you very much!! You help me a lot.