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.

Fiscal Week Calculation in Power BI - Apr 2019 Calendar

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.

Fiscal Week Calculation in Power BI - Apr 2018 Calendar

Fiscal Week Calculation in Power BI - Apr 2020 Calendar

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)

Fiscal Week Calculation in Power BI - Calendar 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..

Fiscal Week Calculation in Power BI - Result

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 

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI