How do you create column for fiscal year that is different from calendar year with Label

[lorenz0210]

Hello everyone, 

I am trying to create our fiscal calendar with the following logic/conditions and label in the slicer:

*Sep, Oct, Nov and Dec 2023 will be labeled as "2023 Q4", while Jan-Dec of the following year should show the normal quarter as Q1, Q2, Q3, Q4. 

I have created a fiscal year calendar below: 

FY Calendar =

VAR _calendar =

    CALENDAR( "9/1/2023","12/31/2024")

RETURN

    ADDCOLUMNS (

        _calendar,

        "YEAR", YEAR ( [Date] ),

        "MonthNumber", MONTH ([Date] ),

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

        "Quarter", "QTR" & FORMAT( [Date], "Q"),

        "QuarterNumber", FORMAT( [Date], "Q"),

        "MonthYearNumber", FORMAT([Date], "yy mm"),

        "Month Year", FORMAT([Date],"mmm yyyy"))

I have been dealing with for 2 days and having no luck. It sounds easy but already having headache. 

I am not sure if I am missing condition. 

Hope somebody can help. 

[v-zhangti]

Hi, @lorenz0210 

You can try the following methods.

Column:

Q = "Q"&QUARTER([Date])

Label = IF(YEAR([Date])=YEAR(TODAY()),YEAR([Date])&" "&[Q],IF(YEAR([Date])=YEAR(TODAY())+1,[Q]))

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lorenz0210]

hi @v-zhangti , 

Appreciate the help. Is there a way we can adjust the Sept-Dec of 2023 to Q4 and then Normal Quarters for the 2024 year? Also, is it possible we can sort the month number as September being the 1st on the calendar since it is the start of our fiscal year. 

Apologies as I am new and still learning.

Similar to this: 

[lorenz0210]

Hi again @v-zhangti , 

I've tried again creating a new column measure that will show FYQuarter using: 

FYQuarter =

"FY" &IF(MONTH('Date'[Date])>=8, YEAR('Date'[Date]),YEAR('Date'[Date]))&"

Quarter"&CEILING(IF(MONTH('Date'[Date])>=8, YEAR(MONTH('Date'[Date])-8)/4,(MONTH('Date'[Date])+4)/3),1)

 But I am getting a different result and not getting the desired result. 

Any help will be greatly appreciated!

Thank you in advance.

[v-zhangti]

Hi, @lorenz0210 

Please show the output you currently expect in Excel or a picture.

Best Regards

[lorenz0210]

Here is the expected result: 

[v-zhangti · Accepted solution]

Hi, @lorenz0210 

You can try the following methods.

Column = IF(YEAR([Date])=YEAR(TODAY())&&MONTH([Date])>=9,YEAR([Date])&" "&"Q4",YEAR([Date])&" "&[Q])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.