Forum Discussion
Sort dates by financial month order
When i use my date data in a report i want my months to be in the order of financial month (Apr=1, May=2......). My solution was to create a Calculated column in the same table as my dates as below.
FinancialMonthNumber = IF(MONTH(tblPurchase[PurchaseDate])<4,MONTH(tblPurchase[PurchaseDate])+9,MONTH(tblPurchase[PurchaseDate])-3)
This returns a column with the correct Financial Month Number for each date but when i then tell Power BI Desktop to sort my dates column by the new calculated column i get this message.
Any ideas?
Shaun
Hi JustShaun,
Based on my research, a workaround is recreating the FinancialMonthNumber in the Query Editor. If you go to Edit Queries, you can use the Add Custom Column feature in the Add Column tab in the ribbon. Use this formula:
if Date.Month([FullDateAlternateKey])<4 then Date.Month([FullDateAlternateKey])+9 else Date.Month([FullDateAlternateKey])-3
Once you Close and Apply, you can then go to the Data pane and sort your dates column by FinancialMonthNumber. Here is the similar thread for your reference.:smileyhappy:
Regards
Hi JustShaun
You need to create a financial month column in date table / calendar. I have used below calculation for the same
FiscalMonth = SWITCH(MONTH(Calendar[DateKey].[Date]),1,10,2,11,3,12,4,01,5,02,6,03,7,04,8,05,9,06,10,07,11,08,12,09)
I hope this way can sort your data by financial month.
All the best!
Anupam
3 Replies
Hi JustShaun,
Based on my research, a workaround is recreating the FinancialMonthNumber in the Query Editor. If you go to Edit Queries, you can use the Add Custom Column feature in the Add Column tab in the ribbon. Use this formula:
if Date.Month([FullDateAlternateKey])<4 then Date.Month([FullDateAlternateKey])+9 else Date.Month([FullDateAlternateKey])-3
Once you Close and Apply, you can then go to the Data pane and sort your dates column by FinancialMonthNumber. Here is the similar thread for your reference.:smileyhappy:
Regards
Hi JustShaun
You need to create a financial month column in date table / calendar. I have used below calculation for the same
FiscalMonth = SWITCH(MONTH(Calendar[DateKey].[Date]),1,10,2,11,3,12,4,01,5,02,6,03,7,04,8,05,9,06,10,07,11,08,12,09)
I hope this way can sort your data by financial month.
All the best!
Anupam
A great way to place a dynamic table in any report in future which creates a British finanical year (April to March) and filterable by all fields with the correct ordering is to create this calculated table (just select 'new table' in the normal view. Once created You would just need to change the date column to a date and 'sort by' the month by month number and day by day number and then create the relationships you wish. It does everything you need:
DateTable = ADDCOLUMNS (
CALENDAR (MIN(insert your table date column here),MAX(insert your table date column here)),
"Day",FORMAT([Date],"DDDD"),
"DayNum",WEEKDAY([Date]),
"Year", FORMAT([Date],"YYYY"),
"Month", FORMAT([Date],"MMMM"),
"MonthNum",SWITCH(MONTH([Date]),
1, "10",
2, "11",
3, "12",
4, "01",
5, "02",
6, "03",
7, "04",
8, "05",
9, "06",
10, "07",
11, "08",
"09" ),
"Qtr", SWITCH( MONTH([Date]),
1, "Qtr4",
2, "Qtr4",
3, "Qtr4",
4, "Qtr1",
5, "Qtr1",
6, "Qtr1",
7, "Qtr2",
8, "Qtr2",
9, "Qtr2",
10, "Qtr3",
11, "Qtr3",
"Qtr3" ),
"MthYr",CONCATENATE(FORMAT([Date],"MMM"),FORMAT([Date]," YYYY")),
"QtrYr",CONCATENATE(SWITCH( MONTH([Date]),
1, "Qtr4",
2, "Qtr4",
3, "Qtr4",
4, "Qtr1",
5, "Qtr1",
6, "Qtr1",
7, "Qtr2",
8, "Qtr2",
9, "Qtr2",
10, "Qtr3",
11, "Qtr3",
"Qtr3" ),FORMAT([Date]," YYYY")),
"FinYr", SWITCH(TRUE(),Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),YEAR([Date])&"-"&YEAR([Date])+1),
"QtrFinYr", CONCATENATE( SWITCH( MONTH([Date]),
1, "Qtr4",
2, "Qtr4",
3, "Qtr4",
4, "Qtr1",
5, "Qtr1",
6, "Qtr1",
7, "Qtr2",
8, "Qtr2",
9, "Qtr2",
10, "Qtr3",
11, "Qtr3",
"Qtr3" )," "&SWITCH(TRUE(),Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),YEAR([Date])&"-"&YEAR([Date])+1))
)
