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.
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!
Solved! Go to 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.
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?
Hi @aarenellsworth ,
Is the [Month] fiscal month?
Please refer these formulas.
Best Regards,
Jay
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!
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.
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.
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.
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
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |