Start table with first month of fiscal year

[aarenellsworth]

I'm trying to build a table based on fiscal years, which start October 1.  Anyone know how to get the table to start with October instead of January?  Thanks!

[Syndicate_Admin]

Hello community, I'm new using power bi, it's not clear to me why it would have to multiply, or what that part means?

[v-jayw-msft]

Hi @aarenellsworth ,

Is the [Month] fiscal month?

Please refer these formulas.

FY = "FY"&year(EDATE('Table'[date],9))

FM = format(EDATE('Table'[date],9),"MMMM")

Sort = MONTH('Table'[date])
Create the visual like below and sort [FM] by [sort].

Best Regards,

Jay

[aarenellsworth]

Thanks Jay!  This didn't really work for me, but I didn't tweak it much because hnguy71's solution worked on the first try.  I appreciate the help!

[hnguy71 · Accepted solution]

Hi @aarenellsworth ,

WIthin your date table, you would need to have another column indicating the fiscal month number:

FiscalMonthNumber = 

VAR _FirstFiscalMonth = 10
VAR _CurrMonthNum = MONTH([Date])

RETURN

_CurrMonthNum - _FirstFiscalMonth + 1 + 12 * (_CurrMonthNum < _FirstFiscalMonth )

Then you would select your Month Text column, and then select Sort By Column and select your new column FiscalMonthNumber.

[aarenellsworth]

Thank you!  This worked for me.

I made a new column and wrote:

Then I made a "Month Name" column and I sorted it by the first column I made.

Month Name = FORMAT([Encounter Date], "mmmm")

[OwenAuger]

Hi @aarenellsworth 

In short, you need to add a sort-by column for your Month column.

I generally create a "Fiscal Month" column, corresponding to the month of the fiscal year.

• October => 1
• November => 2
• etc...

You can do this any number of ways, either in the data source or in Power BI.

In Power Query or DAX, I would add a fixed number of months to each date, and take the calendar month of that date. For years starting October, you would add 3 months.

A snippet of M code defining Fiscal Month in your case could be:

Date.Month(Date.AddMonths([Date],3))

After adding the "Fiscal Month" column, set Month to sort by "Fiscal Month" (docs on Sort By).

Regards,

Owen

[aarenellsworth]

Thank you!