Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 ) ) ) ) ) ) ) ) ) ) ) )
Solved! Go to 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
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
Thank you very much!! You help me a lot.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
84 | |
64 | |
52 | |
49 |
User | Count |
---|---|
211 | |
87 | |
80 | |
69 | |
60 |