The company that I work for was purchased mid year this year. The old company reported on sales results on a normal calendar month while the new one is on a 4,4,5 fiscal month program. So my problem is how to make that transition in power BI. The attached file has a date table that changes the scheme as of 5/1/2019. The rest of the date table prior to that is normal calendar months. I had to do that manually which is fine (you will notice the right most columns have fiscal month info. that becomes different from the calendar month starting 5/24/19.
I have not implemented the new date table yet because I am anticipating that it will wreak havoc with a number of DAX calcs that I have that use ‘sameperiodlastyear’ time intelligence functions. Does anyone know if I will be able to compare future years under the new calendar with prior year YTD type analyses? Any advice on how to make this transition smooth?
This is an interesting scenario and I don’t know if there is an easy answer here.
Due to the way calculations will run depending on the calendar then to me you really need to make a decision around how you want things to be reported going forward.
If the new normal is the 445 calendar then my suggestion would be to setup up everything to report that way. Maybe that’s what everyone will be use to know going forward anywhere.
Unfortunately there no easy and seamless way to run one calculation from one point and then a different one with another. I mean, you could theorectically do it, by saying run this time intelligence technique up until this date then jump to this other one after that date, but really that to me is just overcomplicating it.
Hopefully I’ve given you some ideas here that can assist.
I’m not sure if you will find this helpful, but thought I’d share.
I’ve worked for two companies that had this type of transition. In both cases I’ve set up reporting to the new date pattern and communicated the update to the relevant parties (it’s helpful to provide a re-stated fiscal period calendar for the prior years as well as the current one) . This works well for YTD and YOY analysis, but isn’t great for financial reporting where the data needs to tie to the GL.
My solution for financial reporting was to create a custom date table as well. I don’t have any time intelligence built into those reports, but the fiscal periods reflect the correct dates so you can manually select what is needed on a report for YTD by using a slicer or the filter pane, but definitely not an ideal solution for YOY reporting. If you happen to find a solution for this, I’d love to hear about it.
Hi @geoffellis, we’ve noticed that no response has been received from you since December 31, 2019. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
Thank you Jennifer. When you made the transition, did you just alter the date table to reflect the new fiscal years going forward or did you retroactively change past years as if it had been reported on a 4,4,5 calendar?
I’m only using PBI for sales reporting and not for financial reporting so that likely makes it easier.
Thank you. For future years, it is only important that I be able to compare to prior years. Are you suggesting that I retroactively change the entire date table as if we had been on a 4,4,5 calendar the whole time? Or just change the date table moving forward?
Also, with DAX, I will do some research but the simple formulas like ‘sameperiodlastyear’ --do they only work on a standard calendar month?
When we made this transition, I altered prior years as though they were also in the 4,4,5 format for all sales data. That made comparing to the same period last year much simpler. It also helps with forecasting sales trends (note that if you have seasonality in your sales for Easter and Thanksgiving, those two can fluctuate between different periods).
Okay, thank you. In order to make the sales data match from prior year quarters and years, I think I will change the dates in the records to match the fiscal year. For example, this year, our fiscal year ended 12/27/19. The prior year, it ended 12/28/18. I think I will move the sales data from 12/29-12/31 back to 12/28 so the total year over year is comparable. In future years, I will not have to do this.