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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
rosiebilton23
Frequent Visitor

Order months in financial year in table

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.

 

rosiebilton23_0-1707214183061.png

Any help would be greatly recieved.

Thanks

Rosie 

1 ACCEPTED SOLUTION
CPasten
Frequent Visitor

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())
)

 

View solution in original post

1 REPLY 1
CPasten
Frequent Visitor

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())
)

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.