Forum Discussion
Trouble with Date Table starting in July
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
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
2 Replies
- PhilipTreacy
Super User
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