Forum Discussion

StephenGW's avatar
StephenGW
Helper II
5 years ago
Solved

Fiscal Calendar

All,   I need a way to setup a fiscal calendar table. The fiscal year looks like this.   Starts 12/27/20 and ends 12/25/21 this also uses a 4,5,4 setup where month 1 has 4 weeks, 2 has 5 weeks, 3...
Need Help
mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

You should be able to adapt this expression to meet your needs in a custom column.

 

let

FYstartmonth = 10,
refdate = if Date.Month([Date])>=FYstartmonth then #date(Date.Year([Date]), FYstartmonth, 1) else #date(Date.Year([Date]) - 1, FYstartmonth, 1)
in
Number.RoundUp(1+ Duration.TotalDays([Date] - refdate)/7,0)

 

Pat

StephenGW's avatar
StephenGW
Helper II
4 years ago

mahoneypat 

 

It's still a little off. I think maybe because the start and end of the months is not always on the first or last day of the month. 

 

This screenshot shows it is starting at week 2 when this is the first 3 days of the fiscal year for me and should be the start of week 1. 

 

Then you can see in this one it should start over with week 1 on 1/2/22 but it is starting with week 1 on 1/1/22 and then week 2 on 1/2/22. 

 

All I could see to adapt the code was to change the FYstartmonth = 10 to FYstartmonth = 1?

 

Any help would be great!

 

Thanks,