Order months in financial year in table

[rosiebilton23]

Hi all,

Im sure this is easy to fix but I am at a loss. See screenshot below, my table is is showing January at the front of the table, rather than after Demeber. I have tried a number of ways to creat a month and year column but nothing seems to be working. FYI our financial year is July-June. The transactions on this report have a date against them, in this format 01/01/2024.

Any help would be greatly recieved.

Thanks

Rosie 

[CPasten · Accepted solution]

Have you tried to create an auxiliar calendar table to get Year-Month?

_Calendar =
var _Init = MIN( Table[DateColumn] )
var _Ending = MAX( Table[DateColumn] )
var _Dates = CALENDAR( _Init , _Ending )
RETURN
ADDCOLUMNS( _Dates,
"DayOfMonth", DAY([Date]),
"DayOfYear", DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1,
"DayOfWeek", FORMAT([Date], "dddd"),
"IDDayOfWeek", WEEKDAY([Date], 2),
"nWeek", WEEKNUM(''[Date] - 1),
"DayOffset" , INT([Date] - TODAY()),
"IDMonth" , MONTH([Date]),
"Month" , FORMAT([Date], "mmm"),
"Quarter", "Qter. " & FORMAT(ROUNDUP( MONTH([Date]) / 3 ,0),"00"),
"Semester", "Smter. " & FORMAT(ROUNDUP( MONTH([Date]) / 6 ,0),"00"),
"FullYear" , YEAR([Date]) ,
"YearMonth", FORMAT([Date], "mmmm - yyyy"),
"IDYearMonth" , FORMAT([Date], "yyyymm"),
"MonthOffset" ,( (12 * YEAR([Date])) + MONTH([Date]) ) - ( (12 * YEAR(TODAY()) + MONTH(TODAY()))),
"YearOffset" , YEAR([Date]) - YEAR(TODAY())
)