Custom fiscal year, month and quarter calendar in PowerPivot

[cgrieco]

Greetings,

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.

=IF([Day]<22,MONTH(dCalendar[Dates]),if([Day]>21,IF([MonthNumber]=12,1,MONTH(dCalendar[Dates])+1)))

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

=FORMAT([FiscalMonthNumber],"mmm")

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!

Thanks!

[Chihiro · Accepted solution]

Try this.

=FORMAT(DATE(2017,[FiscalMonthNumber],1),"mmm")

[cgrieco]

Thanks!

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?