cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## DAX Formulas for Fiscal Year, Fiscal Quarter and Month Short Name

1. First Step

Create 2 new column on your DATA report:

1. Month = MONTH(Table[Date])

2. Year = YEAR(Table[Date])

2. Fiscal Year

Creat a new column for Fiscal Year:

Fiscal Year = IF(AND([Year] =2012,[Month]<=12),"FY13",

IF(AND([Year] =2013,[Month]<=6),"FY13",

IF(AND([Year] =2013,[Month]<=12),"FY14",

IF(AND([Year] =2014,[Month]<=6),"FY14",

IF(AND([Year] =2014,[Month]<=12),"FY15",

IF(AND([Year] =2015,[Month]<=6),"FY15",

IF(AND([Year] =2015,[Month]<=12),"FY16",

IF(AND([Year] =2016,[Month]<=6),"FY16",

IF(AND([Year] =2016,[Month]<=12),"FY17",

IF(AND([Year] =2017,[Month]<=6),"FY17",

IF(AND([Year] =2017,[Month]<=12),"FY18",

" ")))))))))))

3.  Fiscal Quarter

FiscalQrt=IF(([MonthOfYear]>=1 && [MonthOfYear]<4),"FQ3",

IF(([MonthOfYear]>=4 && [MonthOfYear]<7),"FQ4",

IF(([MonthOfYear]>=7 && [MonthOfYear]<10),"FQ1","FQ2")))

4. Month Short Name

MonthShortName=IF([Month]=1,"1-Jan",

IF([Month]=2,"2-Feb",

IF([Month]=3,"3-Mar",

IF([Month]=4,"4-Apr",

IF([Month]=5,"5-May",

IF([Month]=6,"6-Jun",

IF([Month]=7,"7-Jul",

IF([Month]=8,"8-Aug",

IF([Month]=9,"9-Sep",

IF([Month]=10,"10-Oct",

IF([Month]=11,"11-Nov","12-Dec")))))))))))

52 REPLIES 52
New Member

Have you got a way to help when the start of the fiscal period (aka "Month") varies due to being 28 day cycle (Except the year before a leap year it is 5 weeks instead of 4 for period 13)?  For example, Period 1 this year is Jan29-Feb25, but last year was Jan30-Feb26.  And sometimes the begin of a new period is in the middle of a month like period 8 is Aug13  -Sep9.  I am really struggling with it and then with the leap year scenarios as well.

Helper I

Could try calculating the day number of the year Solved: Day number of year - Microsoft Power BI Community

Then dividing by 28, round up this figure and this should give period.

To deal with the leap year you could replace the divide by 28 with something to calculate days in the month two months ahead e.g. IF(DAY ( EOMONTH ( [Date], 2) )=29,29,28).

So if I am understanding the question correctly then something along these lines might be what youre after:

FY Monthly Period =
Var Period = ROUNDDOWN(
(DATEDIFF ( DATE ( YEAR ( DateTable[DATE] ), 1, 1 ), DateTable[DATE], DAY ) +1)
/ IF(DAY ( EOMONTH ( DateTable[DATE], 2) )=29,29,28),
0)-1
Return If (Period=0,13,Period)
Frequent Visitor

Hi I dont have date collumn,

I had just these two collums

1. Ship year = 2018,2019,2020,2021...

2. Ship quarter = 1,2,3,4

and I want to calculate/create fiscal year as 2018-2019

quarter 2,3,4 from 2018 & quarter 1 from 2019

does anybody know dax query for this..

Frequent Visitor

Create Fiscal Month From April

Fiscal_MM =
var temp =if(Dim_Date[FMM]>=4,Dim_Date[FMM]-3,Dim_Date[FMM]+9)
return
temp

Key for sorting date as per fiscal year in Ascending order

Sort_by_Fiscal =
CONCATENATE(Dim_Date[Year],
SWITCH([Fiscal_MM],
1, "01", 2, "02", 3, "03", 4, "04"
, 5, "05", 6, "06", 7, "07", 8, "08"
, 9, "09", 10, "10", 11, "11", 12, "12"
)  )

New Member

Here's the approach I took to display the financial year and quarter for the Australian financial year, which runs from 1 July - 30 June, in a single column.

``Fin Year and Qtr = "FY" & IF(MONTH(table[Date])<=6,YEAR(table[Date]),YEAR(table[Date])+1) & "-Q" & SWITCH(MONTH(table[Date]),7,"1",8,"1",9,"1",10,"2",11,"2",12,"2",1,"3",2,"3",3,"3",4,"4",5,"4",6,"4")``

Just update "table[Date]" with the date field from your table and it will display as FY2023-Q1, FY2023-Q2, etc.

New Member

How would you go about A fiscal calendar that strecthes across months - we have one that seems to be week based.  It is 13 periods.  Each is 4 weeks except the occasional period 13 with 5 weeks, but I can't see a pattern for when it is 5 weeks.  It starts in either the first week of Feb or 2nd week of February

Frequent Visitor

Hi All,
I have worked out the solution to get the Financial Year, Qty and Month without having to create the individual month columns.

In this example, my next financial year starts on the 1st October so October 2021 is the start of FY22.

For the financial year, look for the [date], MONTH and if this is equal to or larger than our first financial month (10 Oct), we take the YEAR of the date and add 1, else we use the YEAR of the date:

FinYear = if(MONTH(Table[Date])>=10,YEAR(Table[Date])+1,YEAR(Table[Date]))

In the linear calendar Oct would be the fourth quarter, so to calculate the financial year quarter, look for the [date], QUARTER and if this is equal to or larger than 4, we take the QUARTER of the date and minus 3, else we use the QUARTER of the date and add 1:

FinQtr = if(QUARTER(Table[Date])>=4,QUARTER(Table[Date])-3,QUARTER(Table[Date])+1)

For number of the month in the financial year, look for the [date], MONTH and if this is equal to or larger than our first financial month (10 Oct), we take the MONTH of the date and minus 9, else we use the MONTH of the date and add 3:

FinMonth = if(MONTH(Table[Date])>=10,MONTH(Table[Date])-9,MONTH(Table[Date])+3)

Regular Visitor

Hey, thank you so much for this, but I'm not able to get the Quarter. Can you help me solve mine?? Our FY begins on Sept. (Q1 = Sept - Nov, Q2 = Dec - Feb, Q3 = Mar - May, Q4 = Jun - Aug). I tried using >=3 then -2, then +2 but I just can't get it. Can you help me out? Thank you so much once again!!

Helper I

Or if you want quarters from Sept as a single formula:

Fin Quater =

CEILING((MONTH('Calendar'[Date]) + -(ROUNDUP(MONTH('Calendar'[Date])/8,0)*12-16)) / 3, 1 )

Regular Visitor

This worked out great! I don't quite understand it, I'll take it apart and study it, but this worked. Thank you so much John!!

Helper I

No problem! The formula is basically the same as my previous post:

Fin Quarter =

VAR x =  IF(MONTH('Calendar'[Date]) <94, -8)
RETURN
CEILING((MONTH('Calendar'[Date]) + x) / 31 )

In the single formula -ROUNDUP(MONTH('Calendar'[Date])/8,0)*12-16) replaces the IF statement as it evaluates to either 4 or -8 depending on the month number. More maths than DAX!
Helper I

could use something like this:

VAR x =  IF(MONTH('Calendar'[Date]) <9, 4, -8)
RETURN
CEILING((MONTH('Calendar'[Date]) + x) / 3, 1 )
Super User

Hi,

Try this calculated column formula

Quarter = if(Calendar[Month number]>11,"Q2",if(Calendar[Month number]>=9,"Q1",if(Calendar[Month number]>=6,"Q4",if(Calendar[Month number]>=3,"Q3","Q2"))))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hey, let me take a look and get back. I am due for some pay it forward credits!! Can I get back to you on Mon!?

New Member

Hi,  Can you help me get this right please 😂

When I use the above if(, , ) I get Token "then" expected error.  When I use the following, I get the following

A cyclic reference was encountered during evaluation

if(Date.Month(#"Product List"[SWG Date])>=10)

then Date.Year(#"Product List"[SWG Date])+1

else Date.Year(#"Product List"[SWG Date])

Helper I

Hi, you dont need to wrap the if statement in brackets when using power query, dax uses if (,,) whereas power query uses if then else.

if Date.Month(#"Product List"[SWG Date])>=10

then Date.Year(#"Product List"[SWG Date])+1

else Date.Year(#"Product List"[SWG Date])

Are you applying this to a new column or trying to replace the [SWG Date] values?  This might be where your cyclical reference is coming from.

Frequent Visitor

EXACTLY the wheel I didn't want to recreate... 🙂 THANKS!! Knew this had been solved before now! LOL

Frequent Visitor

Hi,

If my Fiscal Year starts on June, what will be the DAX formula for FinQtr?

Frequent Visitor

June is in the Second quarter, so look for the [date], QUARTER and if this is equal to or larger than 2, we take the QUARTER of the date and minus 1, else we use the QUARTER of the date and add 1:

FinQtr = if(QUARTER(Table[Date])>=2,QUARTER(Table[Date])-1,QUARTER(Table[Date])+1)

Helper I

I have been using a generated calendar using a similar formula to create financial year, quarter and month and put a leading 0 on the month to help with the sort order, and then just join my date field to that, this is for a UK financial year.

Dates =
GENERATE (
CALENDAR (min(MainTable[DateCol]), max(MainTable[DateCol])),
VAR currentDay = [Date]
VAR day = DAY(currentDay)
VAR month = MONTH (currentDay)
VAR quarter = QUARTER(currentDay)
VAR year = YEAR (currentDay)
VAR FinMonth = if (len(if(month<4,month+9,month-3))=1,"0"&if(month<4,month+9,month-3),if(month<4,month+9,month-3))
VAR FinQuarter = if(quarter=1,4,quarter-1)
VAR FinYear = if(month<4,year-1,year)
RETURN ROW (
"day", day,
"month", month,
"quarter", quarter,
"year", year,
"Fin Month", "M " & FinMonth,
"Fin Quarter", "Q " & FinQuarter,
"Fin Year", FinYear )
)

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors