cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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

Highlighted
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

 

 

 

Highlighted
Regular 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?

Highlighted
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
Highlighted
Advocate I
Advocate I

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

))

Highlighted
Advocate I
Advocate I

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

Highlighted
Advocate I
Advocate I

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  🙂 

.Condiitional Format.jpg

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

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

Highlighted
New Member

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 Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors