quarter doesn't follow well with fiscal months

[sportive92003]

Hello,

I begin my fiscal year at month 9. All my programmation is quiet ok, I just have problem with "FiscalQuarter" , it begins at 0!  and "FY&YQuarter" doesn't follow well . 

Dates =

VAR FirstCY = YEAR( FIRSTDATE(Fusionner1[date_de_la_facture]))

VAR FirstCM = MONTH(FIRSTDATE(Fusionner1[date_de_la_facture]))

VAR FirstFY = IF(FirstCM <=8, FirstCY - 1, FirstCY)

VAR LastCY = YEAR( LASTDATE(Fusionner1[date_de_la_facture]))

VAR LastCM = MONTH(LASTDATE(Fusionner1[date_de_la_facture]))

VAR LastFY = IF(LastCM <=8, LastCY, LastCY + 1)

VAR DatesTable =

CALENDAR("01/09/"&FirstFY, "31/08/"&LastFY)

RETURN

ADDCOLUMNS(DatesTable,

"Year", YEAR([Date]),

"FiscalYear", "FY" &IF(MONTH([Date]) >=9, YEAR([Date]) + 1, YEAR([Date])),

"CalendarMonth", MONTH([Date]),

"FiscalMonth", IF(MONTH([Date]) >= 9, MONTH([Date]) - 8, MONTH([Date]) + 4),

"CalendarQuarter", QUARTER([Date]),

"FiscalQuarter", "Q"& IF(MONTH([Date]) >= 9, QUARTER([Date]) - 3, QUARTER([Date]) + 1),

"FY&YQuarter", "FY" & IF(MONTH([Date]) >= 9, YEAR([Date]) + 1, YEAR([Date])) &  " - Q" & IF(MONTH([Date]) >= 9, QUARTER([Date]) - 2, QUARTER([Date]) + 2),

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

"MonthOrderID", IF(MONTH([Date]) >=9, YEAR([Date]) + 1, YEAR([Date])) * 100 + IF(MONTH([Date]) >= 9, MONTH([Date]) - 8, MONTH([Date]) + 4),

"WeekDay", IF(WEEKDAY([Date], 2) < 6, "WeekDay", "Weekend"),

"Day", FORMAT([Date], "dddd")).

Any idea? 

Thank you

Marika

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[Bobp]

I would change the calculation of the Fiscal Quarter ... your logic currently would only work if your first Fiscal period is the first month of a Calendar Quarter.

An easier way is to calculate the quarter number using the Month (or in your case the Fiscal Month) rather than the offset from the Calendar Quarter.

//Fiscal
"Fiscal Month" , VALUE(IF(MONTH([Date]) >= 9, MONTH([Date]) - 8, MONTH([Date]) + 4)),
"Fiscal Quarter" , VALUE(ROUNDUP((IF(MONTH([Date]) >= 9, MONTH([Date]) - 8, MONTH([Date]) + 4)) / 3 , 0)),

Hope this helps,

Bob

[sportive92003]

Thank you very much Bob, seems to work! But how to format number fiscal month to name ? Fiscal Month number 9, I would like that appears "Sept (9)", "Oct (10)", etc...

And "FY&YQuarter" I still not found a solution 🤔. It seems easy, but finally for me I struggle