cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joanperez Frequent Visitor
Frequent Visitor

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

27 REPLIES 27
cbaldock Frequent Visitor
Frequent Visitor

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

Hi,

 

This formula isn't working for me. When I try creating the column it asks for me to add a third requirement for the IF function. If I try adding one it then returns the error of too many requirements. Can you help me at all? 

 

 

Error: "Argument '3' in IF function is required." 

kcantor Super Contributor
Super Contributor

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

It appears that you are, in fact, missing an argument. It should be If then, and else. You have all of the ifs and thens but you are missing an else. Put a ,0 before you begin the parenthesis at the end and see if that works for you.

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Sean Super Contributor
Super Contributor

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

For that many conditions SWITCH statements are so much easier to read and write Smiley Happy

Internally they are transformed into nested IFs

EDIT: No need for all the opening "(" and closing ")" that comes along with the nested IFs

 

MonthShortName =
SWITCH (
    TRUE (),
    [Month] = 1, "1-Jan",
    [Month] = 2, "2-Feb",
    [Month] = 3, "3-Mar",
    [Month] = 4, "4-Apr",
    [Month] = 5, "5-May",
    [Month] = 6, "6-Jun",
    [Month] = 7, "7-Jul",
    [Month] = 8, "8-Aug",
    [Month] = 9, "9-Sep",
    [Month] = 10, "10-Oct",
    [Month] = 11, "11-Nov",
    "12-Dec"
)

 

joanperez Frequent Visitor
Frequent Visitor

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

Solution for calculate the “Fiscal Year”, without date ranges condition (dynamic solution).

 

Scenario:

The formula for calculate the Fiscal Year for the “X” company is from July 1st to June 30th of the next year.

Example:

  • From July 1, 2015 to June 30, 2016 the fiscal year is “FY16”
  • From July 1, 2016 to June 30, 2017 the fiscal year is “FY17”

Expression

Fiscal Year =

IF ‘Date’ Month <= 6,

THEN ‘Year’

ELSE ‘Year’ + 1

 

DAX Expression
Note: The expected Output is “The phrase FY plus the last 2 digits of the Fiscal Year”

 

DAX Formula:

 

Fiscal Year =
  CONCATENATE("FY",
                IF(MONTH('Table'[Date]) <=6,
                       VALUE(FORMAT('Table'[Date],"YY")),
                       VALUE(FORMAT('Table'[Date],"YY")) +1
                     )
                                )

 

 

Table = Table Name

Date = Date Column Name

cbaldock Frequent Visitor
Frequent Visitor

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

This works but the display isn't showing the "0" for years '00 to '09. Can you help me out? Thanks. 

tBronX33 Frequent Visitor
Frequent Visitor

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

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

tBronX33 Frequent Visitor
Frequent Visitor

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

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

sbstern Frequent Visitor
Frequent Visitor

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

This is really helpful, thanks. We have a July-June fiscal year, so I had to adjust the "IsCurrentFiscalYear" column to:

 

IsCurrentFiscalYear = if((year(Now())='Date'[FiscalYear]-1 || year(now())+1='Date'[FiscalYear]),1,0)

nsiddall Frequent Visitor
Frequent Visitor

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

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)

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 236 members 2,457 guests
Please welcome our newest community members: