Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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?
Solved! Go to Solution.
¿ @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-...
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
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
What, are you sure?
https://docs.microsoft.com/en-us/dax/calendarauto-function-dax
Just 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.
¿ @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-...
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
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
55 | |
55 | |
38 | |
29 |
User | Count |
---|---|
78 | |
62 | |
45 | |
40 | |
40 |