Determining First Date of Fiscal Quarter

Power BI Data Model
1

Hi!

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?

2

Try something like this

First Quarter Date =

VAR CurrentQ = Dates[Quarter]
VAR CurrentY = Dates[Year]

RETURN
CALCULATE( MIN( Dates[Date] ),
FILTER( ALL( Dates ), Dates[Quarter] = CurrentQ &amp;&amp; Dates[Year] = CurrentY ) )

You might need to adjust a few things to match your date, but this sort of logic should get you there.

1 Like
3

Yeah that’s the one Sam! Much cleaner, thanks!