sql-bi / DaxDateTemplate

Power BI template to create a Date table in DAX
MIT License
264 stars 76 forks source link

Implement additional settings for weekly fiscal year #44

Open marcosqlbi opened 3 years ago

marcosqlbi commented 3 years ago

Implement settings to enable the generation of a weekly calendar with the following requirements:

The fiscal year begins on the Saturday closest to 1 July and ends on Friday closest to 30 June. The accounting year is split into four accounting quarters, each consisting of 13 weeks. The accounting quarter is split into three accounting months using the “5-4-4” method (that is, the first month in a quarter consists of five weeks; the second and third months consists of four weeks each). Periodically, like last year, to ensure the end of the fiscal year closest to 30 June, the fiscal year will consist of 53 weeks for fiscal year reporting periods.

vgeldbr commented 3 years ago

Found further details:

For management reporting purposes, the accounting period is defined as a set of 5 or 4 weeks which all end on Fridays. The number of weeks in a period is determined by the rule that each quarter of the financial year is broken into three periods. The first period lasts 5 weeks, and the other two each last 4 weeks. This is referred to as the 5-4-4 cycle. Periodically, to ensure that the fiscal year always ends on the Friday closest to 30 June, the fiscal year will consist of 53 weeks. Week 53 is added into the June period so that, in such a year, the last quarter has a 5-4-5 structure. This cycle facilitates closing the entity's books on a consistent day of the week, which aligns with our internal business processes (e.g., submission of weekly timesheets and weekly utilization reporting).