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

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

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

rkalantri Frequent Visitor
Frequent Visitor

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


@sbstern wrote:

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)



hello,

I am trying to use your formula, but i am stuck and not able to understand.

My Fiscal (Financial) year is from April to March.

We call FYear as FY1314 ( 1stApril 2013 to 31st march 2014)

I need to Come up with IsCurrentFYear, IsCurrentMonth.

 

Please help me. Thanks

 

Current Fin Year = if((year(Now())=dCalender[Financial Year]-1 || year(now())+1=dCalender[Financial Year]),1,0)

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

 error.jpg

 

 

 

KEerkes Frequent Visitor
Frequent Visitor

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

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?

nsiddall Frequent Visitor
Frequent Visitor

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

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
Kinsey Frequent Visitor
Frequent Visitor

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

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

))

Kinsey Frequent Visitor
Frequent Visitor

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

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

DandyDan Frequent Visitor
Frequent Visitor

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

 

* 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  Smiley Happy 

.Condiitional Format.jpg

achinchillab Frequent Visitor
Frequent Visitor

Re: 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

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

achinchillab Frequent Visitor
Frequent Visitor

Re: 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

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

sbi Frequent Visitor
Frequent Visitor

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

This will be better, maybe? 

MonthShortName=Format([Date], "mm-mmm")

 

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: 102 members 1,581 guests
Please welcome our newest community members: