{"id":3884,"date":"2021-12-29T17:50:20","date_gmt":"2021-12-30T01:50:20","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3884"},"modified":"2022-01-17T00:01:13","modified_gmt":"2022-01-17T08:01:13","slug":"how-to-add-a-column-with-fiscal-month-or-fiscal-week-number-not-starting-from-jan-1","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3884","title":{"rendered":"How to Add a Column with Fiscal Month or Fiscal Week Number Not Starting From Jan 1?"},"content":{"rendered":"\n

Part I – Fiscal Month<\/strong> <\/h3>\n\n\n\n

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

Create a “Standard Month” column using MONTH([Value])<\/p>\n\n\n\n

For example, create a “Custom Month” column for the Fiscal month starting from April 1:<\/p>\n\n\n\n

Custom Month = \n    SWITCH(TRUE(),\n        [Standard Month] >= 4,[Standard Month] - 3,\n        9 + [Standard Month]\n    )\n\n<\/code><\/pre>\n\n\n\n
Part II – Fiscal Week<\/h3>\n\n\n\n
Calculating Fiscal Year, Quarter, or Month columns isn\u2019t too difficult in the Date Table in Power BI, but calculating fiscal week becomes slightly tricky. In this post, I\u2019ll share a dynamic solution where you can customize the calculation for week numbers as per your fiscal year.<\/p>\n\n\n\n
Let\u2019s dive in!<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The Logic<\/h3>\n\n\n\n
Let\u2019s take a look at 1st April 2019 (starting of Indian FY). It\u2019s 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.<\/p>\n\n\n\n
\"Fiscal<\/figure>\n\n\n\n
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.<\/p>\n\n\n\n
\"Fiscal<\/figure>\n\n\n\n
\"Fiscal<\/figure>\n\n\n\n
And the week counting progresses from there on until the end of financial year i.e. 31st Mar.<\/p>\n\n\n\n
Financial Week Calculation in Power BI \u2013 Creating a Column in Date Table<\/h3>\n\n\n\n
I start with a simple Calendar (or a Date Table)<\/p>\n\n\n\n
\"Fiscal<\/figure>\n\n\n\n
To add a new column for fiscal week calculation I use the following DAX code.<\/p>\n\n\n\n
Fiscal Week = \n--Inputs--\nVAR WeekStartsOn = \"Mon<\/strong>\"\nVAR FiscalStartMonth = 4<\/strong>\n\n--Calculation--\nVAR FiscalFirstDay = \n    IF(\n        MONTH('Calendar'[Date]) < FiscalStartMonth,\n        DATE(\n            YEAR('Calendar'[Date])-1,\n            FiscalStartMonth,\n            1\n        ),\n        DATE(\n            YEAR('Calendar'[Date]),\n            FiscalStartMonth,\n            1\n        )\n    )\nVAR FilteredTableCount = \n    COUNTROWS(\n        FILTER(\n            SELECTCOLUMNS(\n                GENERATESERIES(\n                    FiscalFirstDay,\n                    'Calendar'[Date]\n                ),\n                \"Dates\",\n                [Value]\n            ),\n        FORMAT([Dates],\"ddd\") = WeekStartsOn\n        )\n    )\nVAR WeekNos = \n    IF(\n        FORMAT(FiscalFirstDay,\"ddd\") <> WeekStartsOn,\n        FilteredTableCount + 1,\n        FilteredTableCount\n    )\nRETURN\n    \"Week \" & WeekNos<\/pre>\n\n\n\n
Note a couple of things<\/p>\n\n\n\n
  1. I have 2 inputs to make this dynamic \u2013