Dates Table to Capture Quarters in Fiscal Year

[tlwest86]

The DAX below works perfectly for my Dates table; however, I cannot get my fiscal year or Q1 to start in October.   All my reports display Q1 as January.  Can someone assit with getting my 1st Quarter to begin with October? 

Dates =

VAR BaseCalendar =

CALENDARAUTO (9)

RETURN

GENERATE (

BaseCalendar,

VAR BaseDate = [DATE]

VAR YearDate = YEAR (BASEDATE)

VAR MonthNumber = MONTH (BASEDATE)

VAR Quarter = QUARTER(BASEDATE)

VAR FiscalYear =

RETURN ROW (

"Day", BaseDate,

"Year", YearDate,

"Month Number", MonthNumber,

"Quarter", BaseDate,

"Month", FORMAT (BaseDate, "mmmm"),

"Year Month", FORMAT (BaseDate, "mmm yy")

))

[amitchandak]

@tlwest86 , this link has all 12 possible calendar (based on start month) with qtr start date

refer

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

get the pbix from : https://www.dropbox.com/s/klxcd8d3g21gran/Oct_2_Sep_Qtr_Cal.pbix?dl=0

[tlwest86 · Accepted solution]

This DAX resolved my issue.  Thanks so much for the replies! 

Quarter No = IF(Dates[Month Number] in {10, 11, 12}, "Qtr 1", IF(Dates[Month Number] in {1, 2, 3}, "Qtr 2", IF(Dates[Month Number] in {4, 5, 6}, "Qtr 3", IF(Dates[Month Number] in {7, 8, 9}, "Qtr 4"))))

[Greg_Deckler]

@tlwest86 - Yeah, all the DAX date and time "intelligence" functions assume a standard calendar year. You will need custom quarters calculation. My DAX Cookbook has a recipe for non-standard quarters, it is Recipe 2 in Chapter 2. https://github.com/gdeckler/DAXCookbook

[parry2k]

@tlwest86 if you have a fiscal calendar that starts from Oct 01st, you have to make your own logic to calculates Quarter date range and use that in a measure, inbuilt time intelligent functions are not fiscal calendar aware whereas YTD time intelligence functions are fiscal year aware, you can pass end date of the fiscal calendar in your dax function for example TOTALYTD.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡