Financial Year Starting on the 1st Monday Instead of January 1st

[zakirkhan_bi]

An Unusual Request from a Client

 

Fiscal Year Calculations

A client recently approached me with a somewhat unconventional request. They needed a dashboard, but here’s the twist: In their organization, the financial year doesn’t start on January 1st like it does in most places. Instead, it begins on the first Monday of every year. However, if January 1st happens to fall on a Monday, that’s when their financial year kicks off.

The client offered $80 for the project. I did my best to tackle this challenge, but after two days of effort, I was ready to give up and cancel the order. Just then, an idea struck me, and I decided to give it a shot — and to my relief, it worked!

In this article, I’ll walk you through how I solved this problem.

 

Creating the Calendar Table and Data Model

1. Setting Up the Data

Here’s the data we’ll be working with:

 

Data

2. Creating the Calendar Table

First, I created a Calendar Table. Below is the code used to generate this table:

Calendar = 
ADDCOLUMNS(
    CALENDAR( MIN( 'Sales Data'[CustomerKey] ), MAX( 'Sales Data'[OrderDate] ) ),
    "Year_exp", YEAR( [Date] ),
    "Month Nr", MONTH( [Date] ),
    "Month", FORMAT( [Date], "mmmm" ),
    "Week Nm", FORMAT( [Date], "ddd" )
)

And here is the Calendar Table:

 

Calendar Table

 

3. The Key Solution: The Week_exp Column

Next, I created a column called Week_exp. This column was crucial in solving the problem. Here’s the code I used:

Week_exp = 
VAR WeeksInYear = WEEKNUM( 'Calendar'[Date], 2 )
RETURN
IF(
    FORMAT( STARTOFYEAR( 'Calendar'[Date] ), "ddd" ) <> "Mon",
    WeeksInYear - 1,
    WeeksInYear
)

• The variable WeeksInYear gives us the total number of weeks in a year.
• The STARTOFYEAR function returns the very first date of the year.

 

Start of Year Date

4. Accounting for the Starting Day

The FORMAT function gives us the day name of that date. If the day name is not Monday, we subtract 1 from WeeksInYear.

 

Name of the Day of Start of Year

For instance, if a year has 53 weeks and doesn’t start on a Monday, the week will start from 0 and go up to 52.

 

5. Creating the Week Column

Afterward, I created a Week column in the calendar table. Here’s the code:

Week = 
IF(
    'Calendar'[Week_exp] = 0,
    CALCULATE(
        MAXX(
            ALL( 'Calendar'[Week_exp] ),
            'Calendar'[Week_exp] 
        ),
        YEAR( 'Calendar'[Date] ) = YEAR( 'Calendar'[Date] ) - 1
    ),
    'Calendar'[Week_exp]
)

Handling Week 0

• When Week_exp is 0, the CALCULATE function goes back to the previous year using this filter:

YEAR( 'Calendar'[Date] ) = YEAR( 'Calendar'[Date] ) – 1

The MAXX function then picks the maximum week value from the previous year; otherwise, it returns the same value as Week_exp.

The Result

Here’s what the result looks like:

 

Week column

 

6. The Outcome

In this case, the first date was not a Monday, so the new financial year didn’t start on January 1st, 1935. Instead, it began on January 7th, 1935. This is why the week shows 53 and the year shows 1934.

Here’s the code I used to calculate the year:

Year = 
VAR _year = YEAR( 'Calendar'[Date] )
RETURN
IF(
    'Calendar'[Week_exp] = 0,
    _year -1,
    _year
)

 

Year Column

As you can see, since January 1st, 1935 is not a Monday, the year doesn’t change. Week 53 extends from December 31st, 1934, to January 6th, 1935. The new financial year then begins.

 

By breaking down the problem and implementing this solution, I was able to meet the client’s unique requirements. This approach could be useful in other scenarios where non-standard fiscal years are in play.

This is how 1934 Year’s Week 53 looks like:

 

Dates of Week 53 of 1934

And this is how 1935 Year’s Week 1 looks like:

 

Dates of Week 1of 1935

The End…..