Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dynamic Sorting Based on Current Month

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, ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

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