Power Query - Fiscal Month Start- and End Date

Question

I am working intensively on a Fiscal Calendar generated within Power Query. One of my last challenges is to determine the start- and end date as well as the day number for our fiscal months. The structure is like this:

Date    Fiscal Month    Fiscal Year
28.12.2020  December    2020
29.12.2020  December    2020
30.12.2020  December    2020
31.12.2020  December    2020
01.01.2021  December    2020
02.01.2021  December    2020
03.01.2021  December    2020
04.01.2021  January     2021
05.01.2021  January     2021
06.01.2021  January     2021
07.01.2021  January     2021
08.01.2021  January     2021
09.01.2021  January     2021

How would you determine this information within power query?

Edit: Adding Fiscal Week calculation:

if Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7) = 0 then
    Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date]) - 1, 12, 31)) - (Date.DayOfWeek(#date(Date.Year([Date]) - 1, 12, 31), Day.Monday) + 1) + 10) / 7)
   else if (Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7) = 53 and (Date.DayOfWeek(#date(Date.Year([Date]), 12, 31), Day.Monday) + 1 < 4)) then
    1
   else
    Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7)

Andrey Nikolov Andrey Nikolov · Accepted Answer · 2021-02-25T10:01:15

Assuming that the new fiscal year starts on the first Monday, then you can use Date.WeekOfYear and Date.DayOfWeek functions to check when it starts. So for Fiscal Year it could be:

if (Date.WeekOfYear([Date]) > 1
    or Date.DayOfWeek(#date(Date.Year([Date]), 1, 1), Day.Monday) = 0) then   
    Date.Year([Date])
else
    Date.Year([Date]) - 1

Similarly, the Fiscal Month will be:

if (Date.WeekOfYear([Date]) > 1
    or Date.DayOfWeek(#date(Date.Year([Date]), 1, 1), Day.Monday) = 0) then   
    Date.MonthName([Date])
else
    Date.MonthName(Date.AddMonths([Date], -1))

The start date of the fiscal year can be calculated based on the day of week of January 1st:

#date([Fiscal Year], 1, Number.Mod(7 - Date.DayOfWeek(#date([Fiscal Year], 1, 1), Day.Monday), 7) + 1)

The end date of the fiscal year is a day before the start of the next one:

Date.AddDays(#date([Fiscal Year] + 1, 1, Number.Mod(7 - Date.DayOfWeek(#date([Fiscal Year] + 1, 1, 1), Day.Monday), 7) + 1), -1)

And the day number can be calculated based on the difference between the current date and the start date:

Duration.Days([Date] - [Fiscal Year Start Date]) + 1

If Week Number is the name of the column with the fiscal week number, then the day of the fiscal month can be calculated like this:

if [Week Number] <= 4 then [Day number]
else if [Week Number] <= 8 then [Day number] - 28
else if [Week Number] <= 13 then [Day number] - 56
else if [Week Number] <= 17 then [Day number] - 91
else if [Week Number] <= 21 then [Day number] - 119
else if [Week Number] <= 26 then [Day number] - 147
else if [Week Number] <= 30 then [Day number] - 182
else if [Week Number] <= 34 then [Day number] - 210
else if [Week Number] <= 39 then [Day number] - 238
else if [Week Number] <= 43 then [Day number] - 273
else if [Week Number] <= 47 then [Day number] - 301
else [Day number] - 329

The 53th week will be allocated to the last month.

I guess you will use these custom columns to build a calendar table. You will also need a numeric fiscal month, so you can order months properly. It is the same as Fiscal Month, but use Date.Month instead of Date.MonthName in it.

Power Query - Fiscal Month Start- and End Date

1 Answers