Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Oskar
Helper I
Helper I

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?

1 ACCEPTED 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

View solution in original post

2 REPLIES 2
Oskar
Helper I
Helper I

What, are you sure?
https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

Oskar_0-1643789787819.png

 

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

Oskar_1-1643789850199.png

 

But the documentation mention minDate and maxDate. But i dont get where that is defined.

Oskar_2-1643789887787.png

 

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.