445 Fiscal Calendar

[Anonymous]

Hello Team

I have been stuck on this question for a while now. I have read all the articles here in the community as well as the posting on SQLBI.com around this solution.

Is it possible to create a custom calendar that follows the 445 method for accounting. That is each year is divided into 13 week periods starting from a date of your choosing. For example I want the start of the peroid to begin on a Monday and run for 4 consecutive Mondays. Then repear for the next 4 Mondays. Then repeat for the next 5 mondays. Like this

Begin Date	End Date	Period Name	Week Length
1/07/2019	28/07/2019	P1 2019/20	4
29/07/2019	25/08/2019	P2 2019/20	4
26/08/2019	29/09/2019	P3 2019/20	5
30/09/2019	27/10/2019	P4 2019/20	4
28/10/2019	24/11/2019	P5 2019/20	4
25/11/2019	29/12/2019	P6 2019/20	5

This article does not do exactly what i need

Nor does this webpage

I know I can create a manual calendar in Excel and import this but I wanted to push my knowledge on this topic.

Thank you all

[camargos88 · Accepted solution]

Hi @Anonymous ,

Check this file: Download PBIX 

[Anonymous]

Hello Ricardo 

I am constantaly amazed by the skills in this community. I dont know how long this took you to write but I spent maybe 5 hours going nowhere. Very good thank you.

I am not sure what I did wrong but I was only getting a 4 on all the week numbers. Can you think why that may have been the case? I started the query on the 1/07/2018. for 50 periods. That was a bit odd. I was not able to reproduce that and the data looks perfect.

[camargos88]

Hi @Anonymous ,

I'm glad it worked.

I would need to see your code to say why you got only 4 for week numbers.