cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maclura
Frequent Visitor

Date Table - DAX - CALENDAR() - ADDCOLUMNS() - Quarter Issue

Hi,

I create a new Date table with DAX using the CALENDAR() function.
Adding new columns, no problem. When I add a new "Quarter" colum into the table my table stops starting from Jan 1st and move to start from Jul 1st.
I can't understand why. Any help greatly appreciated.

First Variant (correct)

 

 

Date = 
    VAR MinDate = DATE(2016,1,1)
    VAR MaxDate = DATE(2021,12,31)
    RETURN

    ADDCOLUMNS (
        CALENDAR(MinDate, MaxDate),
        "Calendar Year", "CY " & YEAR ([Date])
    )

 

 

Result

image.png

Second Variant (wrong)

 

 

Date = 
    VAR MinDate = DATE(2016,1,1)
    VAR MaxDate = DATE(2021,12,31)
    RETURN

    ADDCOLUMNS (
        CALENDAR(MinDate, MaxDate),
        "Calendar Year", "CY " & YEAR ([Date]),
        "Calendar Quarter", "CQ " & QUARTER([Date])
    )

 

Result

image.png

Where I am wrong?
Thanks

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

Hi, @maclura , no worries, those other before July are not gone with the wind; but the mechnism under the hood is not clear.

Screenshot 2020-12-10 193326.png

View solution in original post

3 REPLIES 3
CNENFRNL
Super User III
Super User III

Hi, @maclura , no worries, those other before July are not gone with the wind; but the mechnism under the hood is not clear.

Screenshot 2020-12-10 193326.png

View solution in original post

maclura
Frequent Visitor

Thank you @CNENFRNL . 
If I was a little more savvy, I would have to get there on my own. But I agree with you, why generated dates are sorted in that way?

maclura
Frequent Visitor

After some other tests, I think it should be something related to a chaching mechanism.
In fact, sorting issues happen not only with rows, but also with columns, when you add more columns into the formula. The new columns are created regardless of their position in the formula.
If you create a new date table, when you run the formula the first time, columns are created in the order they appear into the formula.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors