Forum Discussion
Help with sorting PowerBI Visuals not by calendar date
- 1 year ago
James_White_199 Add a new column in your data model to represent the fiscal year and fiscal month. You can do this using DAX
DAX
FiscalYear = IF(MONTH([Date]) >= 10, YEAR([Date]), YEAR([Date]) - 1)
FiscalMonth = MONTH([Date]) - 9
FiscalMonth = IF(FiscalMonth <= 0, FiscalMonth + 12, FiscalMonth)Ensure that the fiscal months are sorted correctly. You can create a custom sort order for the fiscal months.
DAX
FiscalMonthName = SWITCH([FiscalMonth],
1, "October",
2, "November",
3, "December",
4, "January",
5, "February",
6, "March",
7, "April",
8, "May",
9, "June",
10, "July",
11, "August",
12, "September"
)Then, sort the FiscalMonthName column by the FiscalMonth column.
Replace the calendar year and month in your visual with the new fiscal year and fiscal month columns.
Ensure that the axis of your Stacked Column Chart uses the FiscalYear and FiscalMonthName columns to reflect the correct order.
James_White_199 Add a new column in your data model to represent the fiscal year and fiscal month. You can do this using DAX
DAX
FiscalYear = IF(MONTH([Date]) >= 10, YEAR([Date]), YEAR([Date]) - 1)
FiscalMonth = MONTH([Date]) - 9
FiscalMonth = IF(FiscalMonth <= 0, FiscalMonth + 12, FiscalMonth)
Ensure that the fiscal months are sorted correctly. You can create a custom sort order for the fiscal months.
DAX
FiscalMonthName = SWITCH([FiscalMonth],
1, "October",
2, "November",
3, "December",
4, "January",
5, "February",
6, "March",
7, "April",
8, "May",
9, "June",
10, "July",
11, "August",
12, "September"
)
Then, sort the FiscalMonthName column by the FiscalMonth column.
Replace the calendar year and month in your visual with the new fiscal year and fiscal month columns.
Ensure that the axis of your Stacked Column Chart uses the FiscalYear and FiscalMonthName columns to reflect the correct order.