topic Re: 445 Fiscal Calendar in DAX Commands and Tips

445 Fiscal Calendar

Anonymous — Wed, 13 May 2020 12:30:09 GMT

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

Re: 445 Fiscal Calendar

camargos88 — Wed, 13 May 2020 21:44:16 GMT

Hi Anonymous ,

Check this file: Download PBIX

Re: 445 Fiscal Calendar

Anonymous — Wed, 13 May 2020 23:43:12 GMT

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.

Re: 445 Fiscal Calendar

camargos88 — Thu, 14 May 2020 12:41:03 GMT

Hi Anonymous ,

I'm glad it worked.

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