cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DebbieE Member
Member

Created Date table with DAX but Dates are from 1899

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

Accepted Solutions
DebbieE Member
Member

Re: Created Date table with DAX but Dates are from 1899

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

10 REPLIES 10
Super User
Super User

Re: Created Date table with DAX but Dates are from 1899

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





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




DebbieE Member
Member

Re: Created Date table with DAX but Dates are from 1899

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)
 
 
Super User
Super User

Re: Created Date table with DAX but Dates are from 1899

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





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




DebbieE Member
Member

Re: Created Date table with DAX but Dates are from 1899

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
))
Super User
Super User

Re: Created Date table with DAX but Dates are from 1899

@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





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




DebbieE Member
Member

Re: Created Date table with DAX but Dates are from 1899

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.

Super User
Super User

Re: Created Date table with DAX but Dates are from 1899

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





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




DebbieE Member
Member

Re: Created Date table with DAX but Dates are from 1899

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

DebbieE Member
Member

Re: Created Date table with DAX but Dates are from 1899



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