Sorting a column chart with actual month on the rightmost position

[Olifer]

Dear Power BI Community,

I'd appreciate any hint on the following issue:

I have created a column chart showing certain values per month and applied a relative date filter showing me the last 12 months from today. What I get is a diagram with the months sorted in chronological order (Jan - Feb - .... - Dec.). However, I would like to see the months sorted in a way that the current month (e. g. August) is on the rightmost side of the chart and all other months are to its left. So the sequence I would like my column chart to appear should look like:

September - October - November - December - January - ... - August

Does anybody know a way to achieve this?

Thanks in advance and best regards

Oliver

[Olifer]

Hi Daniel, 

thanks a lot for your proposal! My column is formatted as date/time and I tried setting up an additional calendar table with some sort of index, but I didn't manage to get it working.

My next idea was to calculate within the same table a new column which concatenates the difference between today and the date in the respective row (in "months") with the name of the month, so this column looks e. g. as follows:

25 July

32 December

34 October

09 November

Then my sorting for the past 12 months in the visual is ok (since August 2022 would be 0, July 2022 would be 1, ....), but it looks a little bit stupid since there is a number in front of the month's name which does not reflect the "usual" number of the month (e. g., July would be the seventh month, so having a "1" before is disturbing).

Not sure whether I manage to express myself clearly - sorry.....

Any advice is still highly appreciated...

Best regards

Oliver

[danielwelch · Accepted solution]

Hi @Olifer ,

So first you want a separate calendar table.  There are a lot of versions of this but below is a table calc you can use to generate a new table for a financial year.  Just amend theFYSTART/END variable dates to either be manual dates or relate to a min/max date in your data:

FY Calendar =

VAR FirstFYMonth =4

VAR Firstdayofweek = 0

VAR FYSTARTDATE = "01/04/2021"

VAR FYENDDATE = "31/03/2024"

VAR FirstFY = YEAR(FYSTARTDATE)+1*(Month(FYSTARTDATE) >=FirstFYMonth && FirstFYMonth >1)

VAR LastFY = YEAR(FYENDDATE)+1*(MONTH(FYENDDATE)>=FirstFYMonth && FirstFYMonth>1)

RETURN

Generate(

VAR Firstday = DATE(FirstFY -1*(FirstFYMonth >1),FirstFYMonth,1)

VAR Lastday = Date(LastFY +1*(FirstFYMonth =1),FirstFYMonth,1)-1

RETURN

CALENDAR (Firstday,Lastday),

VAR CurrentDate = [Date]

VAR Yr = Year(CurrentDate)

VAR Mn = MONTH (CurrentDate)

VAR Mdn = DAY (CurrentDate)

VAR DateKey = Yr*10000+mn*100+Mdn

VAR wd= Weekday(CurrentDate + 7 -Firstdayofweek,2)

VAR WorkingDay = Weekday(CurrentDate,1) IN {2,3,4,5,6}

VAR FYR = Yr + 1 * (FirstFYMonth >1 && mn >= FirstFYMonth)

VAR Fmn = mn - FirstFYMonth +1 +12 *(mn < FirstFYMonth)

VAR Fqrn = ROUNDUP(fmn/3,0)

VAR Fmqn = MOD(fmn -1,3)+1

VAR Fqr = Format(fqrn,"\Q0")

VAR Firstdayofyear = DATE(FYR-1*(FirstFYMonth>1),FirstFYMonth,1)

VAR Fydn = SUMX(CALENDAR(Firstdayofyear,CurrentDate),1*(MONTH([Date])<>2 || DAY([Date]) <> 29))

Return

ROW(

    "DateKey", INT(DateKey),

    "Seq Day Number", INT([Date]),

    "Year Month", FORMAT (CurrentDate, "MMM YYYY"),

    "Year Month Number", Yr*12+mn-1,

    "Financial Year", "FY" & FYR,

    "FY Number", FYR,

    "FY QTR", "F" & Fqr & "-" & Fyr,

    "FY QTR Number", CONVERT(FYR * 4 +Fqrn - 1, INTEGER),

    "Fisc QTR", "F" & Fqr,

    "Month", FORMAT(CurrentDate,"mmm"),

    "FY Month Number",Fmn,

    "FT Month in QTR Number", Fmqn,

    "Day of Week", FORMAT (CurrentDate, "ddd"),

    "Day of Week Number",Wd,

    "Day of Month Number",Mdn,

    "Day of FY Number", Fydn,

    "Working Day", IF(WorkingDay,1, 0),

    "FY QT No",Fqrn))

Then create a relationship in your schema between the calendar table date and the date in your table with data in.

Then sort the calendar table Year month Column by the year month number and change it to be descending. Then in your Month Axis on your visual take the Year Month field from your Calendar table.

This is assuming that August is the max month calculated. 

Hope this helps.

Daniel.

[Olifer]

I got it working - thanks a lot for your support!

Best regards

Oliver

[danielwelch]

Hi @Olifer 

If you have a calendar table for your data then I would suggest creating an index in the calendar table which you can then sort and order your date value by that index.

Daniel.