topic Re: quarter doesn't follow well with fiscal months in DAX Commands and Tips

quarter doesn't follow well with fiscal months

sportive92003 — Fri, 25 Aug 2023 11:54:18 GMT

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

Re: quarter doesn't follow well with fiscal months

Bobp — Mon, 28 Aug 2023 16:11:47 GMT

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

Re: quarter doesn't follow well with fiscal months

sportive92003 — Wed, 30 Aug 2023 13:39:50 GMT

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

Re: quarter doesn't follow well with fiscal months

technolog — Sun, 24 Sep 2023 08:12:33 GMT

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.