Forum Discussion
Creating new table - date-table : i get different results?
I am creating a date-table using this DAX code.
DATES =
VAR BaseCalendar =
CALENDARAUTO(1)
RETURN
GENERATE (
BaseCalendar,
VAR BaseDate = [Date]
VAR YearDate = YEAR ( BaseDate )
VAR DayDate = DAY ( BaseDate )
VAR MonthNumber = MONTH ( BaseDate )
VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
RETURN ROW (
"Day", BaseDate,
"Year", YearDate,
"Month Number", MonthNumber,
"Month", FORMAT ( BaseDate, "mmmm" ),
"Year Month Number", YearMonthNumber,
"Year Month", FORMAT ( BaseDate, "mmm yy" ),
"MyDate", FORMAT( BaseDate, "yyyy-mm-dd" )
)
)
It all works to get a date table.
But i cant figure out why it only goes back to 2021-02-01
I want it further back in the past and also look a bit more ahead...
I use the exact code in another PowerBI report and there i get a table going back to 1899-01-01 ... that is a bit to much but i want to understand what is happening here... why do i get different results?
how?
¿ @Oskar
My bad partner. Completely misread the document and, as you said, modifies the start/end of the fiscal year.
If you're willing to switch from CALENDARAUTO() to something else, try this: https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-bi
This provides much more user control in creating the Date table and you can modify it as needed.
I hope it helps the mate.
Apologies again!
Theo
2 Replies
- OskarHelper I
What, are you sure?
https://docs.microsoft.com/en-us/dax/calendarauto-function-daxJust changing the value from 1 to 13 to get 13 years range.
The value can only be 1-12 and is based on fiscal year so picking 1 is from January and picking 3 is from March
But the documentation mention minDate and maxDate. But i dont get where that is defined.
How can my other PowerBI report have dates dating back 100 years and still just having 1 set in CALENDARAUTO.
- TheoCCommunity Champion
¿ @Oskar
My bad partner. Completely misread the document and, as you said, modifies the start/end of the fiscal year.
If you're willing to switch from CALENDARAUTO() to something else, try this: https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-bi
This provides much more user control in creating the Date table and you can modify it as needed.
I hope it helps the mate.
Apologies again!
Theo