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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Powell360
Helper I
Helper I

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, 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
)
)
)
)
)
)
)
)
)
)
)
)
1 ACCEPTED SOLUTION
Powell360
Helper I
Helper I

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

View solution in original post

2 REPLIES 2
Powell360
Helper I
Helper I

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors