How to Add a Column with Fiscal Month or Fiscal Week Number Not Starting From Jan 1?

Part I – Fiscal Month

Here is a potential solution assuming you have some kind of data table with a column called Value that has your dates in it:

Create a “Standard Month” column using MONTH([Value])

For example, create a “Custom Month” column for the Fiscal month starting from April 1:

Custom Month = 
    SWITCH(TRUE(),
        [Standard Month] >= 4,[Standard Month] - 3,
        9 + [Standard Month]
    )

Part II – Fiscal Week

Calculating Fiscal Year, Quarter, or Month columns isn’t too difficult in the Date Table in Power BI, but calculating fiscal week becomes slightly tricky. In this post, I’ll share a dynamic solution where you can customize the calculation for week numbers as per your fiscal year.

Let’s dive in!

Since the financial year in India starts in April, I am going to explain this basis the Indian fiscal year but you can customize this to your own fiscal year.

The Logic

Let’s take a look at 1st April 2019 (starting of Indian FY). It’s a Monday (Start of the week). Since the first day of the financial year is a Monday, the 1st week starts from 1st April itself.

This logic will change in year 2018 and 2020. Since the first day of the financial year is not a Monday, so the first Monday in April for both these years will be the 2nd week.

And the week counting progresses from there on until the end of financial year i.e. 31st Mar.

Financial Week Calculation in Power BI – Creating a Column in Date Table

I start with a simple Calendar (or a Date Table)

To add a new column for fiscal week calculation I use the following DAX code.

Fiscal Week = 
--Inputs--
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 4

--Calculation--
VAR FiscalFirstDay = 
    IF(
        MONTH('Calendar'[Date]) < FiscalStartMonth,
        DATE(
            YEAR('Calendar'[Date])-1,
            FiscalStartMonth,
            1
        ),
        DATE(
            YEAR('Calendar'[Date]),
            FiscalStartMonth,
            1
        )
    )
VAR FilteredTableCount = 
    COUNTROWS(
        FILTER(
            SELECTCOLUMNS(
                GENERATESERIES(
                    FiscalFirstDay,
                    'Calendar'[Date]
                ),
                "Dates",
                [Value]
            ),
        FORMAT([Dates],"ddd") = WeekStartsOn
        )
    )
VAR WeekNos = 
    IF(
        FORMAT(FiscalFirstDay,"ddd") <> WeekStartsOn,
        FilteredTableCount + 1,
        FilteredTableCount
    )
RETURN
    "Week " & WeekNos

Note a couple of things

1. I have 2 inputs to make this dynamic –
   • FiscalStartMonth – Write a number here. Since we in India start in April, hence 4 is my input.
   • WeekStartsOn – A 3 letter name of the day. Again we start our week on Mon. And yes it needs to be 3 letters only.
2. Although the code might look intimidating but the logic is dead simple. All I am doing is counting the number of Mondays that pass by until the current row Date.

Use the above code to make a new column in your Date Table and this is how it looks..

It looks good for year 2020

1. We start with Wed, 1st April 2020 – as the 1st week
2. The 1st week continues for 5 days until Sun, 5th April 2020.
3. And then week 2 begins.

Ref: https://www.goodly.co.in/calculate-fiscal-week-in-power-bi/