Forum Discussion
Extra Month Showing Up
Hi Guys,
New to Power BI
I'm trying to populate months for a financial year using this calculated column
Month Financial Year =
VAR SelectedMonth = MONTH('Table 1'[Date])
VAR SelectedYear = YEAR('Table 1'[Date])
VAR FinancialYearStart = 7 -- Financial year starts in July
-- Calculate the Financial Year correctly
VAR FinancialYear =
IF(
SelectedMonth >= FinancialYearStart,
SelectedYear, -- If the month is July or later, use the selected year
SelectedYear - 1 -- If the month is before July, use the previous year
)
RETURN
SWITCH(
TRUE(),
SelectedMonth = 7, "July " & FinancialYear - 1, -- July will show the previous year
SelectedMonth = 8, "August " & FinancialYear - 1, -- August will show the previous year
SelectedMonth = 9, "September " & FinancialYear - 1,
SelectedMonth = 10, "October " & FinancialYear - 1,
SelectedMonth = 11, "November " & FinancialYear - 1,
SelectedMonth = 12, "December " & FinancialYear - 1,
SelectedMonth = 1, "January " & FinancialYear + 1 , -- January will show the selected year
SelectedMonth = 2, "February " & FinancialYear + 1,
SelectedMonth = 3, "March " & FinancialYear + 1,
SelectedMonth = 4, "April " & FinancialYear + 1,
SelectedMonth = 5, "May " & FinancialYear + 1,
SelectedMonth = 6, "June " & FinancialYear + 1
)
In my chart below, why is it showing December 2022? It shouldn't be there. Is there a way to mod the formula to exclude it?
This is just using Table 1 only. There isn't any relationship across the other tables as it's mostly all measures from Table 1
Hi Blitzer ,
The best practice approach to handling financial years in Power BI is to define the financial year logic within the Calendar table rather than in fact tables like Table 1. By centralizing this logic, you ensure consistency, improve performance, and simplify filtering and sorting in reports. Instead of adding a calculated column in Table 1, the financial year should be calculated in the Calendar table using a DAX formula. This formula determines the financial year by checking whether the month is greater than or equal to July (the financial year start). If the month is July or later, it assigns the next year as the financial year; otherwise, it keeps the current year.
Financial Year = VAR FinancialYearStart = 7 VAR SelectedMonth = MONTH('Calendar'[Date]) VAR SelectedYear = YEAR('Calendar'[Date]) RETURN IF(SelectedMonth >= FinancialYearStart, SelectedYear + 1, SelectedYear)For reporting purposes, it is useful to create a formatted column that displays both the month name and the financial year. This ensures the correct labeling of months in visuals.
Month Financial Year = FORMAT('Calendar'[Date], "MMMM") & " " & 'Calendar'[Financial Year]By using this approach, all date-based calculations rely on a single, well-structured table, which enhances report efficiency and makes filtering more intuitive. Once the Calendar table is linked to Table 1 through the Date column, the report visuals should use Calendar[Month Financial Year] instead of any custom financial year column in Table 1. Additionally, applying a filter on Calendar[Financial Year] >= 2023 ensures that only relevant financial years are displayed, automatically removing unwanted entries such as December 2022. This method not only resolves the issue but also follows Power BI modeling best practices, making future maintenance easier and improving report performance.
Best regards,
Blitzer It is showing up because whatever measure/calculation that is displaying the line part of the chart is returning a value ( -100% ). So you need to adjust that calculation to return BLANK and then that month won't show. Alternatively, you can exclude it using the Filters pane.
4 Replies
Hi Blitzer ,
The best practice approach to handling financial years in Power BI is to define the financial year logic within the Calendar table rather than in fact tables like Table 1. By centralizing this logic, you ensure consistency, improve performance, and simplify filtering and sorting in reports. Instead of adding a calculated column in Table 1, the financial year should be calculated in the Calendar table using a DAX formula. This formula determines the financial year by checking whether the month is greater than or equal to July (the financial year start). If the month is July or later, it assigns the next year as the financial year; otherwise, it keeps the current year.
Financial Year = VAR FinancialYearStart = 7 VAR SelectedMonth = MONTH('Calendar'[Date]) VAR SelectedYear = YEAR('Calendar'[Date]) RETURN IF(SelectedMonth >= FinancialYearStart, SelectedYear + 1, SelectedYear)For reporting purposes, it is useful to create a formatted column that displays both the month name and the financial year. This ensures the correct labeling of months in visuals.
Month Financial Year = FORMAT('Calendar'[Date], "MMMM") & " " & 'Calendar'[Financial Year]By using this approach, all date-based calculations rely on a single, well-structured table, which enhances report efficiency and makes filtering more intuitive. Once the Calendar table is linked to Table 1 through the Date column, the report visuals should use Calendar[Month Financial Year] instead of any custom financial year column in Table 1. Additionally, applying a filter on Calendar[Financial Year] >= 2023 ensures that only relevant financial years are displayed, automatically removing unwanted entries such as December 2022. This method not only resolves the issue but also follows Power BI modeling best practices, making future maintenance easier and improving report performance.
Best regards,
Thank you
Blitzer It is showing up because whatever measure/calculation that is displaying the line part of the chart is returning a value ( -100% ). So you need to adjust that calculation to return BLANK and then that month won't show. Alternatively, you can exclude it using the Filters pane.
Thank you
