Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
joanperez
Advocate II
Advocate II

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

‎02-10-2016 12:36 PM

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")))))))))))

Labels:
Message 1 of 53
221,839 Views
52 REPLIES 52
lionelv
Frequent Visitor

‎09-29-2021 12:44 PM

The simpliest way to do that (ex: create a column with "2021-2022", etc.) is to substracting 3 months to the main Date (which means starting the year in April). For exemple, for any date:

FiscalYear=year(DATEADD('Table'[Date], -3, MONTH))&"-"&year(DATEADD('Table'[Date], -3, MONTH))+1

Message 33 of 53
86,215 Views
0
Anonymous
Not applicable

‎05-11-2020 01:55 AM

Hello, 

 

I've tried to use the following section to create a Fiscal Year column:

 

Fiscal Year = 

IF(AND([Year] =2017,[Month]>3),"FY17/18",

IF(AND([Year] =2018,[Month]<4),"FY17/18",

IF(AND([Year] =2018,[Month]>3),"FY18/19",

IF(AND([Year] =2019,[Month]<4),"FY18/19",

IF(AND([Year] =2019,[Month]>3),"FY19/20",

IF(AND([Year] =2020,[Month]<4),"FY19/20"," ")))))))

 

I've tried to do this via creating a custom column and as a measure. Neither is working. 

 

The error when creating a custom column is "Token RightParen Expected". However I have all the right parenthesies I need'; adding or subtracting them makes no difference to the error. 

 

The error in a measure relates to Month and Year not being recognised. 

 

Is anyone able to help me?

 

My aim is to end up with a silcer that has three check boxes in it. The first filters to FY17/18, the next to FY18/19, the third to FY19/20.

 

If anyone can help me I'd really appreciate it. Thanks

Message 32 of 53
112,639 Views
0
MarkPalmberg
Kudo Collector
Kudo Collector

‎10-30-2019 08:24 AM

This is a must-have reference for date column variations.

Message 31 of 53
113,233 Views
0
Anonymous
Not applicable

‎06-04-2019 08:15 AM

My Fiscal year runs from April 1 to March 31.  I found this message very useful in solving my needs.  Here is what I did

 

Step 1.

Create 2 columns, Month and Year  (for my purposes I used a column called Received Date so my columns are titled the same

1.  Received MONTH = tblAssessments[ReceivedDate].[MonthNo]

2.  Received YEAR = tblAssessments[ReceivedDate].[Year]

 

Step 2. 

Create a Fiscal Year Column without having to have YEAR be a calculation of the IF statement

- (Note: Concatenate is limited 2 items in Power BI, I would have preferred to add a "/" between the years

 

Fiscal Year = IF(tblAssessments[Received MONTH]>=4,CONCATENATE(tblAssessments[Received YEAR],tblAssessments[Received YEAR]+1),CONCATENATE(tblAssessments[Received YEAR]-1,tblAssessments[Received YEAR]))
 
example: This code would allow April 2011 to December 2011 to be considered in the fiscal year 2011/2012 and January 2012 to March 2012 to be considered in the fiscal year 2011/2012 

 

Message 29 of 53
113,659 Views
0
KristinS
New Member
In response to Anonymous

‎09-06-2019 01:37 PM

This thread was immenselty helpful.  Thank you!

Message 30 of 53
113,223 Views
0
achinchillab
Frequent Visitor

‎07-04-2017 02:52 PM

1. First Step

    Create 2 new column on your DATA report:

        1. Month = MONTH(Table[Date])

        2. Year = YEAR(Table[Date])

 

2. Fiscal Year

CreatE a new column for Fiscal Year:

FY = IF(AND('Table'[Month Sort]>=4,'Table'[Month]<=12),'Table'[Year]+1,'Table'[Year])

 

Hope this helps.  It works for any year...

Message 28 of 53
121,495 Views
achinchillab
Frequent Visitor

‎07-04-2017 02:48 PM

1. First Step

Create 2 new column on your DATA report:

   1. Month = MONTH(Table[Date])

   2. Year = YEAR(Table[Date])

 

2. Fiscal Year

   Create a new column for Fiscal Year:

   FY = IF(AND('Calendar'[Month Sort]>=4,'Calendar'[Month Sort]<=12),'Calendar'[Year]+1,'Calendar'[Year])

 

Hope this helps...

Message 26 of 53
121,474 Views
Alfonzo_baca
Regular Visitor
In response to achinchillab

‎01-20-2022 09:31 AM

This was really helpful and effective, without having a large Dax

Message 27 of 53
55,610 Views
0
Anonymous
Not applicable

‎05-29-2017 01:21 PM

 

* Great Solution!  

* You can do similar leveraging the Conditional Column .  Not as Dynamic as DAX, but perhaps a little easier for those not ready to enter into the world of DAX.

* Excited for MSFT to make this a control within the Application ~~ Anticipating by the end of FY Q2 of '18  🙂 

.Condiitional Format.jpg

Message 25 of 53
102,435 Views
0
Kinsey
Advocate II
Advocate II

‎05-24-2017 03:49 AM

Cleaned up a little and added finincial year and financial yr/qtr, adjust as required:

 

DateTable = ADDCOLUMNS (
    CALENDAR (DATE(2011,1,1), DATE(2018,01,01)),
    "Day",FORMAT([Date],"DDDD"),
    "Year", FORMAT([Date],"YYYY"),
    "Month", FORMAT([Date],"MMMM"),
    "Quarter", SWITCH( MONTH([Date]),
                1, "Qtr4",
                2, "Qtr4",
                3, "Qtr4",
                4, "Qtr1",
                5, "Qtr1",
                6, "Qtr1",
                7, "Qtr2",
                8, "Qtr2",
                9, "Qtr2",
                10, "Qtr3",
                11, "Qtr3",
                "Qtr3" ),
    "MthYr",CONCATENATE(FORMAT([Date],"MMM"),FORMAT([Date]," YYYY")),
    "QtrYr",CONCATENATE(SWITCH( MONTH([Date]),
                1, "Qtr4",
                2, "Qtr4",
                3, "Qtr4",
                4, "Qtr1",
                5, "Qtr1",
                6, "Qtr1",
                7, "Qtr2",
                8, "Qtr2",
                9, "Qtr2",
                10, "Qtr3",
                11, "Qtr3",
                "Qtr3" ),FORMAT([Date]," YYYY")),
    "FinancialYr", SWITCH(TRUE(),Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),YEAR([Date])&"-"&YEAR([Date])+1),
    "QtrFinYr", CONCATENATE( SWITCH( MONTH([Date]),
                1, "Qtr4",
                2, "Qtr4",
                3, "Qtr4",
                4, "Qtr1",
                5, "Qtr1",
                6, "Qtr1",
                7, "Qtr2",
                8, "Qtr2",
                9, "Qtr2",
                10, "Qtr3",
                11, "Qtr3",
                "Qtr3" )," "&SWITCH(TRUE(),Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),YEAR([Date])&"-"&YEAR([Date])+1))
    )

Message 24 of 53
101,659 Views
0
KEerkes
Regular Visitor

‎04-13-2017 10:58 AM

I added the Month & Year columns to my data.

Then I copied the Fiscal Year Query in my custom column creator.

I got the "no syntax errors" confirmation and clicked to proceed.

 

Upon trying to complete this column addition I'm getting:

 
Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.

 

= Table.AddColumn("Fiscal Year", each 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",
" "))))))))))))

 

 

Please advise. What am I missing?

Message 19 of 53
102,476 Views
0
Jawed
Helper III
Helper III
In response to KEerkes

‎11-10-2017 02:23 AM

Hi there,

 

Thank you for posting this. It did give me the idea on how to work this out. However, I think I have made it even more dynamic. My FY is April - March. Here is the formula that I used which works as well but it is dynamic:

 

FY = IF(FY_Calendar[MonthNo]<=3,"FY"&RIGHT(FY_Calendar[Year]-1,2),"FY"&RIGHT(FY_Calendar[Year],2))

 

 

Message 22 of 53
95,539 Views
duyarra
Frequent Visitor
In response to Jawed

‎05-31-2018 04:57 AM

Just what I needed, thanx!

Message 23 of 53
78,416 Views
0
nsiddall
Frequent Visitor
In response to KEerkes

‎04-13-2017 12:42 PM
Not sure but you may be able to use SWITCH rather than all these nested IFs... also I see you are asking if <=6 and then in next line asking for same year if <=12... unless I'm reading it wrong any number equal to or less than 6 would fall into both statements e.g. 4 is both less than 6 and less than 12! Maybe swap to <=6 and >6 seeing as your month won't go above 12 anyway? Does that make sense?

Nick
Message 20 of 53
84,482 Views
0
Kinsey
Advocate II
Advocate II
In response to nsiddall

‎05-24-2017 03:14 AM

I've put some of this together to make a generic DateTable creator like so, just need to work out how to make 3 month rolling indicators etc:

 

Simply update the date range required in the dates marked bold below (format is Year, Month, Day) then paste in a new table formula bar:

 

DateTable = ADDCOLUMNS (

   CALENDAR (DATE(2011,1,1), DATE(2018,01,01)),

   "Day",FORMAT([Date],"DDDD"),

   "Year", FORMAT([Date],"YYYY"),

   "Month", FORMAT([Date],"MMMM"),

   "Quarter", SWITCH( TRUE(),

               MONTH([Date]) = 1, "Qtr4",

               MONTH([Date]) = 2, "Qtr4",

               MONTH([Date]) = 3, "Qtr4",

               MONTH([Date]) = 4, "Qtr1",

               MONTH([Date]) = 5, "Qtr1",

               MONTH([Date]) = 6, "Qtr1",

               MONTH([Date]) = 7, "Qtr2",

               MONTH([Date]) = 8, "Qtr2",

               MONTH([Date]) = 9, "Qtr2",

               MONTH([Date]) = 10, "Qtr3",

               MONTH([Date]) = 11, "Qtr3",

               "Qtr3" ),

   "MthYr",CONCATENATE(FORMAT([Date],"MMM"),FORMAT([Date]," YYYY")),

   "QtrYr",CONCATENATE(SWITCH( TRUE(),

               MONTH([Date]) = 1, "Qtr4",

               MONTH([Date]) = 2, "Qtr4",

               MONTH([Date]) = 3, "Qtr4",

               MONTH([Date]) = 4, "Qtr1",

               MONTH([Date]) = 5, "Qtr1",

               MONTH([Date]) = 6, "Qtr1",

               MONTH([Date]) = 7, "Qtr2",

               MONTH([Date]) = 8, "Qtr2",

               MONTH([Date]) = 9, "Qtr2",

               MONTH([Date]) = 10, "Qtr3",

               MONTH([Date]) = 11, "Qtr3",

               "Qtr3" ),FORMAT([Date]," YYYY")

))

Message 21 of 53
83,615 Views
0
jarvis84
Regular Visitor

‎01-03-2017 04:27 AM

Thanks for the post, I have adjusted for use in the UK - please like if useful:

 

1. First Step

Create 2 new columns on your DATA report:

 

1. Month = MONTH(Table[Date])

2. Year = YEAR(Table[Date])

 

2. Fiscal Year

Create a new column for Fiscal Year:

 

Fiscal Year = IF(AND([Year] =2013,[Month]<=3),"FY12",

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

IF(AND([Year] =2014,[Month]<=3),"FY13",

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

IF(AND([Year] =2015,[Month]<=3),"FY14",

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

IF(AND([Year] =2016,[Month]<=3),"FY15",

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

IF(AND([Year] =2017,[Month]<=3),"FY16",

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

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

 

3.  Fiscal Quarter

Create a new column for Fiscal Quarter:

 

Fiscal QTR = IF(([Month]>=1 && [Month]<4),"FQ4",

IF(([Month]>=4 && [Month]<7),"FQ1",

IF(([Month]>=7 && [Month]<10),"FQ2","FQ3")))

 

This provides:

 

2013Qtr 2JuneFQ1FY13
2013Qtr 3JulyFQ2FY13
2013Qtr 3AugustFQ2FY13
2013Qtr 3SeptemberFQ2FY13
2013Qtr 4OctoberFQ3FY13
2013Qtr 4NovemberFQ3FY13
2013Qtr 4DecemberFQ3FY13
2014Qtr 1JanuaryFQ4FY13
2014Qtr 1FebruaryFQ4FY13
2014Qtr 1MarchFQ4FY13
2014Qtr 2AprilFQ1FY14
2014Qtr 2MayFQ1FY14
2014Qtr 2JuneFQ1FY14

 

All the best, Oli

Message 17 of 53
81,521 Views
Bola_O
New Member
In response to jarvis84

‎07-18-2021 06:55 AM

Thanks Oli. That was helpful.

Message 18 of 53
47,211 Views
0
nsiddall
Frequent Visitor

‎11-21-2016 07:18 AM

For those of you looking to create a relative Quarter Offset - I used this:

Relative Quarter Offset = (4*YEAR([Date]) + ROUNDDOWN(MONTH([Date])/3,1) - (4*YEAR(TODAY()) + ROUNDDOWN(MONTH(TODAY())/3,1)))

Where 'Date' came from my Dim Date table...

 

This means the current quarter returns a zero - the last quarter returns - 1 etc etc... works a treat!

 

(Quarters based on Jan > Mar, Apr > June, Jul > Sept & Oct > Dec)

Message 14 of 53
143,892 Views
Wang1234
Frequent Visitor
In response to nsiddall

‎05-02-2019 09:53 AM

Fiscal Year Quarter Cut Off works well and it applies to all no matter when the Fiscal Year start, April or October.

 

If you are looking for Fiscal Year CutOff to dynamically display measures of "Current Fiscal Year", "Previous Fiscal Year" and etc.,  Here are the steps and DAX will work.   Please comment if this works, Thank you.

 

Note: Fiscal Year Start from April 1st in this example.

Fiscal Year Number= If(
			Month(Date[date])<= 3, 
				Year(Date[date])-1, 
					Year(Date[date])
			)
Current Fiscal Year Number = If( 
   				Month(today())<= 3, 
					Year(today())-1, 
						Year(today())
			)
Fiscal Year CutOff = Date[Fiscal Year Number]-Date[Current Fiscal Year Number]
Message 15 of 53
63,608 Views
0
Wang1234
Frequent Visitor
In response to Wang1234

‎05-02-2019 09:56 AM

"Fiscal Year CutOff" = 0, Current Fiscal Year

"Fiscal Year CutOff" = -1, Previous Fiscal Year

...

Message 16 of 53
48,421 Views
0
tBronX33
New Member

‎10-24-2016 01:50 PM

I needed to bring back transactions that are in the current fiscal year.  Here is what I did:

 

1.  Created a column called Fiscal Year:

Fiscal Year =
IF(Month(PROJEMPLTRANS[TRANSDATE]) < 11, Value(FORMAT(PROJEMPLTRANS[TRANSDATE], "YYYY")), VALUE(FORMAT(PROJEMPLTRANS[TRANSDATE], "YYYY")) +1)

 

2.  Created a column called Year:

Year = Year(PROJEMPLTRANS[TRANSDATE])

 

3.  Created a column called "IsCurrentFiscalYear"

IsCurrentFiscalYear = If((Year(NOW()) = PROJEMPLTRANS[Fiscal Year]) || (Year(NOW()) = PROJEMPLTRANS[Year] - 1),1,0)

 

Now in my report I simply filtered by the value 1 on the IsCurrentFiscalYear column.

 

Thanks,

 

Tom - Stoneridge Software

Message 10 of 53
119,435 Views

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

View All
Top Solution Authors
View All