cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sbowles Frequent Visitor
Frequent Visitor

How do i create a date table ?

I was trying to create  a date table for filtering.. And ran across this DAX (below) ... thinking it looked useful. 

However when I went to create the table and use it.. Didn't seem to work.. 

 

example 

 

Date =

ADDCOLUMNS (

CALENDAR (DATE (2000, 1, 1), DATE (2025, 12, 31);

“DateAsInteger”; FORMAT ( [Date]; “YYYYMMDD” );

“Year”; YEAR ( [Date] );

“Monthnumber”; FORMAT ( [Date]; “MM” );

“YearMonthnumber”; FORMAT ( [Date]; “YYYY/MM” );

“YearMonthShort”; FORMAT ( [Date]; “YYYY/mmm” );

“MonthNameShort”; FORMAT ( [Date]; “mmm” );

“MonthNameLong”; FORMAT ( [Date]; “mmmm” );

“DayOfWeekNumber”; WEEKDAY ( [Date] );

“DayOfWeek”; FORMAT ( [Date]; “dddd” );

“DayOfWeekShort”; FORMAT ( [Date]; “dddd” );

“Quarter”; “Q” & FORMAT ( [Date]; “Q” );

“YearQuarter”; FORMAT ( [Date]; “YYYY” ) & “/Q” & FORMAT ( [Date]; “Q” )

)

 

I took it from this dude site.. .. Anyone have any suggestions for me on how I can quickly create the date table .. 

http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps...

 

25 REPLIES 25
Highlighted
GilesWalker Established Member
Established Member

Re: How do i create a date table ?

@sbowles The way I create a date table is as follows:

 

Click the insert new table button on the ribbon and copy in below:

 

DateKey = CALENDAR(DATE(2012,01,01),DATE(2017,06,30))

 

Then I add in a new column from the ribbon for each of the following:

 

Year = YEAR(DateKey[Date])
Month number = MONTH(DateKey[Date])
Month = FORMAT(DateKey[Date],"MMM")
Day = FORMAT(DateKey[Date],"ddd")
Week = WEEKNUM(DateKey[Date],1)
Quarter = "Q" & ROUNDUP(MONTH(DateKey[Date])/3,0)
MonthY = FORMAT(DateKey[Date],"MMM")&" " &DateKey[Year]
Day number = DAY(DateKey[Date])
Financial year = IF(DateKey[Month number]>6,DateKey[Year]+1,DateKey[Year])
Financial week = IF(DateKey[Month number]>6,DateKey[Week]-26,DateKey[Week]+26)
Total days = DAY(DATE(DateKey[Year],DateKey[Month number]+1,1)-1)
Financial month number = IF((DateKey[Month number]-6)<=0,DateKey[Month number]+6,DateKey[Month number]-6)
Financial month = FORMAT(DateKey[Date],"MMM")
Index = CALCULATE(COUNT(DateKey[Date]),ALL(DateKey[Date]),FILTER(DateKey,DateKey[Date]<=EARLIER(DateKey[Date])))
Monthy number = DateKey[Year]&DateKey[Month number]

 

I also include the following measures:

 

Measures
Current financial week = IF(WEEKNUM(NOW())-26<=0,WEEKNUM(NOW())+26,WEEKNUM(NOW())-26)
Current financial month = IF(MONTH(NOW())-6<=0,MONTH(NOW())+6,MONTH(NOW())-6)
Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

 

Then I can use these rolling filters:

 

Other formula
+/- 1 months = IF(AND(DateKey[Financial month number]>=[Current financial month]-1,DateKey[Financial month number]<=[Current financial month]),1,0)
Last 30 day = IF(AND(DateKey[Date]>=[Today]-30,DateKey[Date]<=[Today]),1,0)
Last week + two weeks = IF(AND(DateKey[Financial week]>=[Current financial week]-1,DateKey[Financial week]<=[Current financial week]+2),1,0)
Last 14 days = IF(AND(DateKey[Date]>=[Today]-14,DateKey[Date]<=[Today]),1,0)
+/- 30 days = IF(AND(DateKey[Date]>=[Today]-30,DateKey[Date]<=[Today]+30),1,0)

 

Hope this helps.

 

Giles

sbowles Frequent Visitor
Frequent Visitor

Re: How do i create a date table ?

Thanks Giles , this is awesome and very helpful .. :-) 

 

one more question .. 

 

of all of these filters below i am getting the following error  : 

 

+/- 1 months = IF(AND(DateKey[Financial month number]>=[Current financial month]-1,DateKey[Financial month number]<=[Current financial month]),1,0)

 

"A single value for column 'Date' in table 'DateKey' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

GilesWalker Established Member
Established Member

Re: How do i create a date table ?

@sbowles Are you entering the formula as a measure or a column? It needs to be inserted as a column, I think this may be the issue.

PeterBI Regular Visitor
Regular Visitor

Re: How do i create a date table ?

Hi Giles,

 

Thanks for your Suggestion. I get with the first step the first error - Wrong Syntax.

I am using the actual PowerBI Desktop Version. Thanks for a Hint ;-)

 

Kind Regards, Peter

 

2016-06-13 21-02-08.png2016-06-13 21-02-32.png

 

 

sdjensen Senior Member
Senior Member

Re: How do i create a date table ?

Depending on your regional settings you will need to replace the , with ;

 

So try this instead: DateKey = CALENDAR(DATE(2012;01;01); DATE(2017;06;30))

 

if that works you should do the same with all the other steps.

/sdjensen
Super User
Super User

Re: How do i create a date table ?

@PeterBI You have a semi-colon where you should have a comma before "DATE(". In addition, to create this you need to click on "Modeling" - "New Table" and input the code in Table = CALENDAR(DATE(2012,01,01),DATE(2017,06,30))

Near SE WI? Join our PUG Milwaukee Brew City PUG
PeterBI Regular Visitor
Regular Visitor

Re: How do i create a date table ?

Hi sdjensen,

 

this was the solution. Every comma has to be a semicolon in Germany. We have a decimal comma.

PowerBI changes some commas automatic and some not. You have to look very carefully, because some formulas work and some need manual work.

Thanks, Peter

Super User
Super User

Re: How do i create a date table ?

Hi

 

Try to createa new table using Power Query for master Date, The Power Query is 

 

let
Source = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & "'" & Text.Range(Number.ToText([Year]),2,2)),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekStarting = Table.AddColumn(InsertWeekEnding, "WeekStart", each Date.StartOfWeek([Date]), type date)
in
InsertWeekStarting,
#"Invoked FunctionSource" = Source(#date(2016, 1, 1), #date(2016, 12, 31), null),
#"Changed Type" = Table.TransformColumnTypes(#"Invoked FunctionSource",{{"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayInWeek", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Date - Copy", Date.WeekOfYear}}),
#"Renamed Columns" = Table.RenameColumns(#"Calculated Week of Year",{{"Date - Copy", "WeekofMonth"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"DateInt", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Sorting Order", each [Year]*100 + [MonthOfYear]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Sorting Order", Int64.Type}})
in
#"Changed Type2"

 

It will help u

 

dpkndls Frequent Visitor
Frequent Visitor

Re: How do i create a date table ?

we can't calculate our fiscal year from this formula 

Quarter = "Q" & ROUNDUP(MONTH(DateKey[Date])/3,0)

 

My fiscal year start from April & end with March

 

Date Range = "Datekey = CALENDAR(DATE(2014, 04, 01),DATE(2017, 03, 31))"

 

Please suggest me.

 

Thanks