I’ve recently been working on a report that needed data presented at a Quarterly level. We use custom Quarters:
Q1 = Aug, Sep, Oct
Q2 = Nov, Dec, Jan
Q3, = Feb, Mar, Apr
Q4 = May, Jun, Jul
Getting the number of the quarter into the table was pretty easy, adding a Division column on the Fiscal month and rounding up.
I got a bit stuck when I wanted to add a column to return the first date of the quarter, because one quarter goes across two calendar years
I did manage to work it out eventually, although it is a bit ugly. I think that one of those variables might be redundant.
Start of Quarter = VAR FinancialYear = VALUE ( 'Date'[Financial Year] ) VAR MonthNumber = MONTH ( 'Date'[DateKey] ) VAR CalendarYear = YEAR ( 'Date'[DateKey] ) VAR FinancialQuarter = VALUE ( 'Date'[Financial Quarter] ) VAR AdjustedYear = IF ( MonthNumber < 8, FinancialYear, CalendarYear ) RETURN SWITCH ( TRUE (), FinancialQuarter = 1, DATE ( AdjustedYear, 8, 1 ), FinancialQuarter = 2 && MonthNumber = 1, DATE ( CalendarYear - 1, 11, 1 ), FinancialQuarter = 2 && MonthNumber > 1, DATE ( AdjustedYear, 11, 1 ), FinancialQuarter = 3, DATE ( AdjustedYear, 2, 1 ), FinancialQuarter = 4, DATE ( AdjustedYear, 5, 1 ) )
Any ideas on how I could clean it up a little bit?