Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cgrieco
Regular Visitor

Custom fiscal year, month and quarter calendar in PowerPivot

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!

1 ACCEPTED SOLUTION
Chihiro
Solution Sage
Solution Sage

Try this.

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

View solution in original post

2 REPLIES 2
Chihiro
Solution Sage
Solution Sage

Try this.

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

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.