I just ran across the Extended Date Table which I think is awesome. Has anyone had any luck adding to it to include a custom date for the start of a fiscal month? My company begins the fiscal month on the 26th, so for example April would be April 26 - May 25th, May 26th would start the new fiscal month for June.
Hi @Anonymous63,
Give this a go.
Set up your date table and make sure this includes a “Day” column for the day number. Use this [Day] value in a conditional column to pick up the [Month name] when it equals 26 else return a null. Now depending on the start date in your table - this could mean that first date has no value - in such a case you’ll need to include a secondary if statement explicitly supplying a value for this initial date in your Dates table. Then select fill down.
I hope this is helpful
1 Like
I need to ensure that all columns related to the fiscal calendar would reflect properly. Currently everything follows a regular calendar.
Using the example from the original post, the fiscal month of May would run 4/26 - 5/25. If I look at the date table on 5/15, it would show that 4/26 - 4/30 would not be in the current FM (returning false), and that 5/26 - 5/31 would be (returning true).
The last place I worked at started their FY in October, that further complicated everything when trying to do a column such as “Is Current FY?”
Can you elaborate more on additional requirements if any?
Thanks!
1 Like
I am with @Melissa on this one. @Anonymous63 , please upload a Power BI desktop file and data file so that we are able to set the need in a realistic context. If you already have this modeled in Excel format, please send that along as well. Thank you!
When creating the date table, there should be an option to specify what # day of a month that the fiscal month starts on.
Under the current setup, the fiscal months align with calendar months and run 1 - 31 for Jan, 1 - 28 for Feb, 1 - 31 for Mar, 1 - 30 for April, etc. Being able to set the date of the 26 to start FMs would allow for FMs to show as 12/26 - 1/25 for Jan, 1/26 - 2/25 for Feb, 2/26 - 3/25 for Mar, etc.
Please upload a work-in-progress Power BI Desktop file that includes the path and logic Melissa suggested earlier today.
Made an account just to comment on this. I think I know where OP is going with this. We use an offset in starting our fiscal month too. It allows us to close out on the 25th, spend the 26th cleaning things up, and start sending out statements to get into customers hands on the 1st of the next month to pay by the 10th.
If a companies fiscal calendar does not align with the Gregorian calendar, all columns related to fiscal month (period), fiscal quarter, and fiscal year could return incorrect information.
Using OPs example and today’s date – July 11th 2023 – July 26th - 31st will return “True” in the column "Is Current FP? This should be false if my fiscal month (or period) starts on the 26th, July 26 - 31 would be in the next fiscal month. This date table assumes that each fiscal period starts on the first of each month, and ends on the natural last day of each month (28th, 29th, 30th, or 31st).
I believe what OP is asking for is an option to input “This is my fiscal month start date each month” and “This is my fiscal month end date each month” so that “IsCurrentFY”, “IsCurrentFQ” and “IsCurrentFP” return correctly based on the custom fiscal calendar. Something similar to the prompt of asking for an offset # of months for the start of the fiscal year like you currently have.
ystroman, I dont believe a PBIX or XLSX file is going to help here. Its just a matter of knowing that a fiscal month of the 26th - 25th does not line up with a calendar month of 1st - 31st, and could cause incorrect returns in the fiscal calendar related columns if using this date table. It sounds like they are requesting help with a company report so that would probably be proprietary.
At least its not like the last company I was with. We ran 13 fiscal periods, every 4 weeks was a new period.
Dear @MilwTed, Thank you for contributing to the conversation. We have requested a work-in-progress Power BI file from @Anonymous63 so that they can apply the Calendar Logic suggested by @Melissa. This allows other Forum members to benefit from the suggestion and observe the results. It is normal for a Forum Member to anonymize sample files containing business confidential information when creating a Forum Posting. This allows our volunteer Forum Members to accurately assess the question and suggest logic in response to the Forum Poster’s question so that the Poster may update the design and report back if the suggestion solved the issue or need additional help.
The standard banner when opening a New Topic (new Forum Post)
reads as follows:
*** READ FIRST ***
Before you send your question.
Make sure that all details relevant to your question is complete:
- Your current work-in-progress PBIX file - VERY IMPORTANT
- A clear explanation of the problem you are experiencing
- A mockup of the results you want to achieve
- Your underlying data file
Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***
sample.pbix (71.9 KB)
So here is a file showing exactly what OP is talking about. No data necessary.
I added an index column for ease. 26-30 return False when they should return True because my FP starts on the 26th. 56 - 61 returns True when they should return false because my FP ended on the 25th. Both of these occurred because I could not specify my FP start and end dates.
Yes this is the exact problem I am having