Created Date table with DAX but Dates are from 1899

[DebbieE]

I have created a DAX table using CALENDARAUTO But I dont want a date table with dates previous to what is in my data. I dont need dates from 1899. Is there any way I can have a date table that starts at say the start of your data set and ends at the end of the year of your data set? 

Dim Date = VAR BaseCalendar = CALENDARAUTO(12)

RETURN GENERATE (

BaseCalendar,

VAR BaseDate = [DATE]

VAR YearDate = YEAR( BaseDate)

VAR MonthNumber = FORMAT(MONTH( BaseDate),"00")

VAR MonthName = FORMAT(DATE(1,MonthNumber,1),"MMMM")

VAR DayOfWeek = WEEKDAY(BaseDate)

VAR WeekDay = FORMAT(BaseDate,"DDDD")

VAR WeekNo = WEEKNUM(BaseDate)

VAR Quarter = ROUNDUP(Month(BaseDate)/3,0)

RETURN ROW (

"DateKey",YearDate&MonthNumber&FORMAT(DAY(BaseDate),"00"),

"Day", FORMAT(DAY(BaseDate),"00"),

"Year",YearDate,

"Quarter",Quarter,

"Month No", MonthNumber,

"Month",MonthName,

"Day of Week", DayOfWeek,

"Week Day",WeekDay,

"WeekNo", WeekNo

))

[parry2k]

@DebbieE  you can use calendar DAX function to create calendar table and give min and max date from your data table

[DebbieE]

I wanted the date to be dynamic and grow without having to hardcode the dates

I tried this

CALENDAR (MINX (), MAXX ())

But It wont allow me to add my dates to into () when I try and I dont understand why?

(As it when I start adding Start Date It doesnt come through for me to select)

[parry2k]

@DebbieE assuming you have transaction table with date field in it, this is wht you need to do

calendar = 
var startDate = MIN( TranTable[Date] )
var endDate = MAX( TranTable[Date] )
RETURN
CALENDAR(  startDate, endDate)

and then you can add other fields as required. I hope it helps.

[DebbieE]

Im struggling to understand how the above is used to change what I have now Ive hard coded dates

Dim Date = VAR BaseCalendar = CALENDAR (DATE (2017, 1, 1), DATE (2020, 12, 31))

RETURN GENERATE (

BaseCalendar,

VAR BaseDate = [DATE]

VAR YearDate = YEAR( BaseDate)

VAR MonthNumber = FORMAT(MONTH( BaseDate),"00")

VAR MonthName = FORMAT(DATE(1,MonthNumber,1),"MMMM")

VAR DayOfWeek = WEEKDAY(BaseDate)

VAR WeekDay = FORMAT(BaseDate,"DDDD")

VAR WeekNo = WEEKNUM(BaseDate)

VAR Quarter = ROUNDUP(Month(BaseDate)/3,0)

RETURN ROW (

"DateKey",YearDate&MonthNumber&FORMAT(DAY(BaseDate),"00"),

"Day", FORMAT(DAY(BaseDate),"00"),

"Year",YearDate,

"Quarter",Quarter,

"Month No", MonthNumber,

"Month",MonthName,

"Day of Week", DayOfWeek,

"Week Day",WeekDay,

"WeekNo", WeekNo

))

[parry2k]

@DebbieE I guess your question is why calendar and calendarauto workign differently? Not sure what your question is? Since you hard coded the date range, you are getting calendar for that period, whereas calendarauto, automatically calculates the date range from your dataset

[DebbieE]

This was my question,

To get it working I had hard coded the values for CALENDAR which works but its not really what I want

I wasnt sure how to transform what I already have into something thats using a minimum and a maximum date from my data.

I saw the DAX provided but still didnt understand how I could do this. I understand why Calendar and Calendar AUTO work differently.

[parry2k]

@DebbieE did you saw my reply on how to get min and max date which then you can pass to calendar function instead of hard coding.

[DebbieE]

---

---

Yes, that is what I looked at and as I said, I still didnt understand how to apply that to what I already had set up.  Ive decided to not use a DAX Table for dates for the following reasons.

Every time I add a new fields into the Date table all my visuals reset from the date hierarchy to the date causing my visuals to error until I go through and change everything back to hierarchy, I dont want this to happen when I decide to add a new column.

Every time I decide to use the Date table the full DAX code appears in its window and I have to re hide it. This is really annoying when your trying to use fields from the date table

So for this Ive decided to create the date time dimension in my database as a table

[parry2k]

@DebbieE i'm sorry  but not fully sure what was actual issue, may be it is me and not understanding the problem. since you already resolved it but i would still like to know more about the issue, is it possible for you to take screen shot and if possible to share pbix file to look into it further.

[DebbieE · Accepted solution]

Yes, that is what I looked at and as I said, I still didnt understand how to apply that to what I already had set up.  Ive decided to not use a DAX Table for dates for the following reasons.

Every time I add a new fields into the Date table all my visuals reset from the date hierarchy to the date causing my visuals to error until I go through and change everything back to hierarchy, I dont want this to happen when I decide to add a new column.

Every time I decide to use the Date table the full DAX code appears in its window and I have to re hide it. This is really annoying when your trying to use fields from the date table

So for this Ive decided to create the date time dimension in my database as a table