Just been updating my accounting template to use @Melissa extended date table with dynamic start end dates etc. Whole thing is just fantastic and replaces nicely the excel version which I had previously.
One issue I did have was there was no Fiscal Year Start column which I overcame in PQ with the following steps.
Add column to give start of month when fiscal period is 1.
Fill down the column to populate the other periods
This all works fine other than in the first year. So overcame this by bringing in the dates for the year before the first year and filtered this year out at the end.
All works but in my view my approach is a bit clunky. A better way might be to add a column with the min date for each Fisc Year. But my PQ skills don’t extend that far.
So not a biggy post since I have the workaround, but just a heads up that in a future version it would be cool to have the Fiscal Year Start date generated at the outset.
I had clocked that @BrianJ and am using it. But a lot of my DAX to get the YTD financial measures uses DATESBETWEEN financial year start and max(date). The YTD functions do work by adding the argument “3/31”, but this is hardcoded. So moved to DATESBETWEEN to manage any fiscal year.
So a column with financial year start date would be useful.
Maybe if I’d set the original model up with Melissa’s table, I’d have filtered the date table differently.
Anyway as I said earlier, not a biggy. Just a nice to have.
