CODEWITHSUNDEEP
calendarpowerbidax
PGB
PGB

Reputation: 27

Generate Date Table for Quarters Only in PowerBI

I'm trying to generate a table using DAX in PowerBI to use as a date table, however in this case instead of the traditional list of days, I'm trying to generate a list of quarters- specifically the first date of a quarter.

I've tried using CALENDAR to generate days and then convert them to quarters, however I can't seem to find a way to transform the generated days into the first day of the quarter.

QTRMatrix = 
ADDCOLUMNS(
    STARTOFQUARTER(CALENDAR(MIN(Bookings[InvoiceMonth]),TODAY())),
    "Period", "Quarter"
)

This gives me an error stating:

"Function 'STARTOFQUARTER' cannot be used with columns added by ADDCOLUMNS or SUMMARIZE functions."

I've also tried this without ADDCOLUMNS which produces the same error as above.

Could someone please advise how I can achieve this?

Upvotes: 1

Views: 2986

Answers (1)

StelioK
StelioK

Reputation: 1781

The following DAX will produce a very useful calendar table:

Just create two measures for your period bounds:

Min Date :=
MIN( ‘Table’[Date] )

Max Date :=
MAX(‘Table’[Date] )

Then build your calendar based off of these dates:

Dates :=
VAR MinDate = [Min Date]
VAR MaxDate = [Max Date]

VAR BaseCalendar =
    CALENDAR ( MinDate, MaxDate )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate =
            YEAR ( BaseDate )
        VAR MonthNumber =
            MONTH ( BaseDate )
        VAR YrMonth =
            100 * YEAR ( BaseDate )
                + MONTH ( BaseDate )
        VAR Qtr =
            CONCATENATE ( "Q", CEILING ( MONTH ( BaseDate ) / 3, 1 ) )
        VAR YrMonthQtr =
            100 * YEAR ( BaseDate )
                + MONTH ( BaseDate )
                & CONCATENATE ( "Q", CEILING ( MONTH ( BaseDate ) / 3, 1 ) )
        VAR YrMonthQtrDay =
            100 * YEAR ( BaseDate )
                + MONTH ( BaseDate )
                & CONCATENATE ( "Q", CEILING ( MONTH ( BaseDate ) / 3, 1 ) )
                & DAY ( BaseDate )
        RETURN
            ROW (
                "Day", BaseDate,
                "Year", YearDate,
                "Month Number", MonthNumber,
                "Month", FORMAT ( BaseDate, "mmmm" ),
                "Year Month", FORMAT ( BaseDate, "mmm yy" ),
                "YrMonth", YrMonth,
                "Qtr", Qtr,
                "YrMonthQtr", YrMonthQtr,
                "YrMonthQtrday", YrMonthQtrDay
            )
    )

To get first day of quarter simply add a calculated column that flags the first day of each quarter.

Upvotes: 1

Related Questions