How do i create a date table ?

28 Replies

GilesWalker
Skilled Sharer
10 years ago
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
  Regular Visitor
  10 years ago
  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
    Skilled Sharer
    10 years ago
    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
  Helper I
  10 years ago
  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
  - sdjensen
    Solution Sage
    10 years ago
    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.
- dpkndls
  Regular Visitor
  9 years ago
  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
Baskar
Resident Rockstar
10 years ago
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

Advocate I

Just swap all his semi colons with commas, and it works.

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], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

nchambe
Advocate II
8 years ago
I took GilesWalker's solution (it's great) and modified and added some. The formulas are copy-and-paste ready too (no curvy quote format issues). Here's my preferred finished product:
https://sharepointlibrarian.com/2018/02/12/how-to-create-a-powerful-date-table-or-datekey-in-power-bi/
- GilesWalker
  Skilled Sharer
  8 years ago
  nchambe - thanks for the feedback and the great write up on your blog.
  
  One change I have made to the formula for creating the table is similar to yours:
  
  DateKey = CALENDAR(DATE(2017,07,01),DATE(YEAR(NOW())+1,06,30))
  
  This way my end date will always move with the changing of calendar years and gives me the date to the end of the financial year.
- GilesWalker
  Skilled Sharer
  8 years ago
  nchambe - thanks for the feedback and the great write up on your blog.
  
  One change I have made to the formula for creating the table is similar to yours:
  
  DateKey = CALENDAR(DATE(2017,07,01),DATE(YEAR(NOW())+1,06,30))
  
  This way my end date will always move with the changing of calendar years and gives me the date to the end of the financial year.
TheUnicorn
Regular Visitor
9 years ago
TRy This

Date_New =
ADDCOLUMNS (
CALENDAR ( "1-jan-2010", "31-dec-2020" ),
"Date Key", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Month number", FORMAT ( [Date], "MM" ),
"Year Month number", FORMAT ( [Date], "YYYY/MM" ),
"Year Month Short", FORMAT ( [Date], "YYYY/mmm" ),
"Month Name Short", FORMAT ( [Date], "mmm" ),
"Month Name Long", FORMAT ( [Date], "mmmm" ),
"Day of Week Number", WEEKDAY ( [Date] ),
"Day of Week", FORMAT ( [Date], "dddd" ),
"Day of Week Short", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Year Quarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
- dpkndls
  Regular Visitor
  9 years ago
  Thanks,
  
  but its too long to understand & also DAX RUNDOWN also not working when calculate quarter for month which starting from APRIL.
  
  Power BI default counts his FISCAL YEAR from JAN, Is there any option to change DAFAULT fiscal year in POWER BI.??
Anonymous
Not applicable
8 years ago
Sorry if I'm jumping on your thread for my own ends but I used the exact same formula from that website you've mentioned and wondered whether anyone was able to apply a filter on it. I just need to exclude the blanks from a selection slicer on my dashboard as it's taking up too much space. Anyone know how I could do this?
Anonymous
Not applicable
6 years ago
How do I create a column with number of working days in a year for the financial year? monday to friday.

Meaning if it starts in Nov 1
That 11/01/2020 Day Column starts with day 1 and ignores saturdays and sunday in the count

Is this possible?
- edhans
  Community Champion
  6 years ago
  See this formula. You'll have to tweak it if you are not on a calendar year. The [Year] column would need to refer to a [Fiscal Year] for example.
  
  WorksDay Count = VAR CurrentDay = 'Date'[Date] VAR CurrentYear = 'Date'[Year] VAR WorkdayCount = COUNTROWS( FILTER( ALL('Date'[Date],'Date'[IsWorkDay],'Date'[Year]), 'Date'[Date] <= CurrentDay && 'Date'[Year] = CurrentYear && 'Date'[IsWorkDay] = TRUE() ) ) RETURN WorkdayCount
  
  It relies on another column called IsWorkday, which is:
  
  IsWorkDay = WEEKDAY('Date'[Date],2) < 6
  
  With the IsWorkDay column, it is easy to count, filter, or determine if a day is a workday in visuals, measures, etc.
  
  Creating a Dynamic Date Table in Power Query
  - Anonymous
    Not applicable
    6 years ago
    Hi
    
    Thanks that worked perfectly,
    
    However seems like the date table is not working connected with my data. Im trying to calculare the % change day over day. And i get wrong numbers when calculating with my date field on my data table, however when i use the date field from my data table then works but it doesnt exclude weekends as a result i get wrong values every monday of the dates.
    Norm. Price = CALCULATE(sumX(Winter_Contracts_Zema,Winter_Contracts_Zema[Price]), FILTER('Date','Date'[IsWorkDay]= TRUE())) + 'Normalized Value'[Normalized Value Value]
    Norm Price: Its my price field plus a variable [NormValue] = Value from 1 to 5 that changes the formula dynamically.
    This is the DAX I'm using to calculate Last day value, which gives me incorrect values for all mondays as it shows (2) which is the variable im adding to my price field.
    Norm_Price_LD = CALCULATE([Norm. Price],ALLEXCEPT(Winter_Contracts_Zema,Winter_Contracts_Zema[Date]),DATEADD(Winter_Contracts_Zema[Date],-1,DAY))
    
    However, using this DAX doesnt work when using date from date table. Norm Priced just shows 2 in all columns which is the Normalized value. and the DayoDay % still shows 2 for all mondays.
    
    I also tried to add Is work day to my page filter but no luck
    
    ANy ideas?

Forum Discussion

28 Replies

Related content

Creating Date Tables

Create new table

Lakehouse Table Generate Create Table

Creating my own Date Table

Create rows from 2 tables