Calendar() and CalendarAuto(), both functions help you to create a base table for date dimension. They give you a contiguous set of dates which is the primary requirement of any date dimension.
Calendar() function gives you a sequence of dates based on startdate and enddate as its parameters. e.g.
It gives you ten days as
CalendarAuto() scans your model for any column that have DateTime as its datatype and then finds out the minimum date and maximum date across all such columns. Then those minimum and maximum dates are rounded off to the nearest year e.g. let’s say that in the model, there is a table which has a column DateValue and contains say only two rows as shown below
Minimum date is 01/31/2019 so it’s rounded off to the beginning of year 2019 and maximum date is 06/01/2020 so it’s rounded off to the end of year 2020.
It gives 731 rows as 365 days in 2019 and 366 days in 2020 as shown below
By default CalendarAuto() considers calendar year and hence the minimum and maximum dates are rounded to the 1st January and 31st December of their corresponding year. However, if you want to give consideration to fiscal years which may start/end from a different month then you can pass a parameter to CalendarAuto() function as shown in the below snapshot
i.e. passing 6 would say that fiscal year is ending in June which means starting in July.
So, for our previous example where minimum date was 01/31/2019, it’s rounded off to the beginning of year which would be like 07/01/2018 and maximum date, which was like 06/01/2020 would expand upto 06/30/2020.
Both, Calendar() and CalendarAuto() gives you a table with single column named as “Date”.