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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Oskar
Frequent Visitor

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
Frequent Visitor

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors