Generate Date Table for Quarters Only in PowerBI

Question

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?

StelioK · Accepted Answer · 2018-11-03 00:45:58Z

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.

Collectives™ on Stack Overflow

Generate Date Table for Quarters Only in PowerBI

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related