CODEWITHSUNDEEP
powerbidax
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

DAX Convert date to the first day of quarter

While building a calendar table I came across unexpected hard nut to crack.

How to convert date to the first date of the quarter? Here are some examples:

2019-02-01 > 2019-01-01
2019-03-31 > 2019-01-01
2019-04-02 > 2019-04-01

The function STARTOFQUARTER does not work in my calendar. I do not know why.

Calendar = 
GENERATE (
    CALENDAR (
        DATE ( 2016,  1,  1 ),
        DATE ( 2020, 12, 31 )
    ),
    VAR VarDates = [Date]
    var YQ_date  = STARTOFQUARTER( [Date] ) -- this does not work

    RETURN
        ROW (
            "day"     , VarDay,
            "YQ_date" , YQ_date
        )
)

The only option seems to be adding calculated column to Calendar table. But if it is possible to have it straight, then why not have it straight?

Upvotes: 0

Views: 2200

Answers (3)

Alexis Olson
Alexis Olson

Reputation: 40244

How about as an added column?

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2020, 12, 31 ) ),
    "YQ_date", EOMONTH ( [Date], -1 - MOD ( MONTH ( [Date] ) - 1, 3 ) ) + 1
)

Upvotes: 1

Marco Vos
Marco Vos

Reputation: 2967

When you want to build a calendar table with one DAX expression, you cannot use the STARTOFQUARTER function, because it will not work with an in-memory table.

You could use something like this as a workaround though:

Calendar = 
GENERATE (
    CALENDAR (
        DATE ( 2016;  1;  1 );
        DATE ( 2020; 12; 31 )
    );  
    ROW (
        "YQ_date"; DATE( Year( [Date] ); ROUNDUP( MONTH ( [Date] ) / 3; 0 ) * 3 - 2; 1)
    )
)

Upvotes: 1

Marco Vos
Marco Vos

Reputation: 2967

For the STARTOFQUARTER function to work, you need the dates that you expect to be returned by the function in your datetable.

So in the provided sample, you need to add the dates 2019-01-01 and 2019-04-01.

Something like this:

enter image description here

Upvotes: 1

Related Questions