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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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