Calculate Fiscal Week in Power BI

Calculating Fiscal Year, Quarter or Month columns, isn’t too difficult in the Date Table in Power BI, but calculating fiscal week become 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.

Needless to say if you have a better method for a smarter formula, do post it in comments :). I am also going to make changes to my earlier blog post on Fiscal Year Calculations and add Fiscal Weeks to it !

Cheers

 

More DAX Calculations

1. Financial Year Calculations in Power BI
2. Slab / Tiered Calculations in Power BI
3. Sort by Column – Examples
4. Last Year Calculations for Partial Year
5. Top Selling Product Analysis
6. Least Selling Product Analysis