Reply
Frequent Visitor
Posts: 4
Registered: ‎03-15-2016

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

 

Established Member
Posts: 178
Registered: ‎10-18-2015

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

Frequent Visitor
Posts: 4
Registered: ‎03-15-2016

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

Established Member
Posts: 178
Registered: ‎10-18-2015

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.

Regular Visitor
Posts: 18
Registered: ‎04-26-2016

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

 

 

Senior Member
Posts: 332
Registered: ‎01-14-2016

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
Posts: 3,452
Registered: ‎06-25-2015

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 MSBIWI
Regular Visitor
Posts: 18
Registered: ‎04-26-2016

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 Contributor
Posts: 733
Registered: ‎06-03-2016

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

 

Frequent Visitor
Posts: 4
Registered: ‎01-14-2017

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