cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joanperez
Advocate II
Advocate II

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

36 REPLIES 36
clowery
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

Bulldog
Regular 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)

wanwong
Frequent Visitor

Hi,

 

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

JohnSalt
Frequent Visitor

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 )
)
 
 
lionelv
Regular Visitor

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

Anonymous
Not applicable

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

MarkPalmberg
Advocate V
Advocate V

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

Anonymous
Not applicable

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 

 

This thread was immenselty helpful.  Thank you!

achinchillab
Frequent Visitor

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...

achinchillab
Frequent Visitor

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...

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

DandyDan
Advocate I
Advocate I

 

* 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

Kinsey
Advocate I
Advocate I

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

KEerkes
Regular Visitor

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?

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

 

 

duyarra
Frequent Visitor

Just what I needed, thanx!

nsiddall
Frequent Visitor

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

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

))

jarvis84
Regular Visitor

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors