topic Re: How do you create column for fiscal year that is different from calendar year with Label in DAX Commands and Tips

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

lorenz0210 — Wed, 18 Oct 2023 19:14:53 GMT

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.

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

v-zhangti — Fri, 20 Oct 2023 01:51:42 GMT

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.

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

lorenz0210 — Mon, 23 Oct 2023 13:48:54 GMT

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:

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

lorenz0210 — Mon, 23 Oct 2023 23:23:06 GMT

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.

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

v-zhangti — Tue, 24 Oct 2023 11:21:57 GMT

Hi, lorenz0210

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

Best Regards

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

lorenz0210 — Tue, 24 Oct 2023 13:06:47 GMT

Here is the expected result:

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

v-zhangti — Tue, 31 Oct 2023 02:04:56 GMT

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.