Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Custom fiscal year, month and quarter calendar in PowerPivot



After searching for resources to help with my needs I found some useful info to get me part of the way there. But I need specific help I am not finding. Hoping I am in the right place for formulas in PowerPivot in Excel 2013 to get me where I need to go.


My company has fiscal months that start on the 22nd of each month and end on the 21st of the next month.


I have a Powerpivot table that has the following structure


Dates    MonthNumber   Day    Month   FiscalMonthNumber  FiscalMonthName

7/22/17         7                  22       Jul                  8                      CANNOT GET TO WORK



I successfully created FiscalMonthNumber column with the following.




When I attempt to use Format function to get a monthname in FiscalMonthName using




I dont get the intended result I would think I would get.


In the example above. Since I have the correct fiscal month number (of 8), I need to get Aug provided for in the FiscalMonthName column.


Insight needed on how to accomplish this!


Solution Sage
Solution Sage

Try this.


View solution in original post

Solution Sage
Solution Sage

Try this.




Did the trick. I see even though 2017 as the year is in the formula it correctly populates the month for prior years in the calendar table. Is it correct that the formatting of the year hard written into the formula "2017" and the day "1" is not relevant to the output of the Month name but required syntax to get the end result of the Month being selected from the FiscalMonthNumber field?

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors