Trouble with Date Table starting in July

[Anonymous]

Hi,

Hi, I am trying to create a data table for 2020 and 2021. I have a date table defined like this:

Date =

VAR __startDate = DATE ( 2020, 1, 1 )

VAR __endDate = DATE ( YEAR ( TODAY() ), 12, 31 )

VAR __dates = CALENDAR(__startDate,__endDate)

RETURN

ADDCOLUMNS (

__dates,

"Year", YEAR ( [Date] ),

"Month Number", MONTH ( [Date] ),

"Month Name", FORMAT ( [Date], "MMM" ), --use MMMM for full month name, January instead of Jan

"Month", FORMAT( [Date], "MMM, YYYY" ), --use MMMMM for full month name, January instead of Ja

"Month Sort", FORMAT( [Date], "YYYY-MM" ),

"Quarter", "Q" & FORMAT( [Date], "Q, YYYY" ),

"Quarter Sort", FORMAT ( [Date], "YYYY-Q" )

)

and for some reason its starting on July 1 2020 instead of Jan 1, 2020

If I remove the two Quarter columns :

Date =

VAR __startDate = DATE ( 2020, 1, 1 )

VAR __endDate = DATE ( YEAR ( TODAY() ), 12, 31 )

VAR __dates = CALENDAR(__startDate,__endDate)

RETURN

ADDCOLUMNS (

__dates,

"Year", YEAR ( [Date] ),

"Month Number", MONTH ( [Date] ),

"Month Name", FORMAT ( [Date], "MMM" ), --use MMMM for full month name, January instead of Jan

"Month", FORMAT( [Date], "MMM, YYYY" ), --use MMMMM for full month name, January instead of Ja

"Month Sort", FORMAT( [Date], "YYYY-MM" )

)

Then it starts in January as I want.

How do i get it always to start in January?

(I've been taking all sorts of online training and I recall somhere they showed me how to change the fiscal year settings and that likely whats happenning, but I cant find out where I did that)

TIA

Russell

[Anonymous]

Doh!

Thanks, was just not sorted

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

That code works fine for me.  Try sorting the Date column in Ascending order and you'll see 1 Jan 2020.

Regards

Phil