How do i create a date table ?

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.
  - PeterBI
    Helper I
    10 years ago
    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
- Anonymous
  Not applicable
  10 years ago
  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))
  - Anonymous
    Not applicable
    8 years ago
    Hello,
    I have created the date table following your tips and instructions. However when I use the DateKey table in Quick Measure Calculations I receive following error:
    
    Net Revenue YoY% =
    IF(
    ISFILTERED('DateKey'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_YEAR =
      CALCULATE(
       SUM('Project Details_USD'[Net Revenue]),
       DATEADD('DateKey'[Date].[Date], -3, YEAR)
      )
    RETURN
      DIVIDE(
       SUM('Project Details_USD'[Net Revenue]) - __PREV_YEAR,
       __PREV_YEAR
      )
    )
    
    Where do I go wrong? I am not a DAX person, so I copy/paste formulas. I really need to get he Power BI-provided date hierarchy going as I do a lot of reporting using Time Intelligence. I was using Pivot Tables and Tableau Quick Table Calculations.
    
    Thank you in advance,
    Petek
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
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
- Anonymous
  Not applicable
  9 years ago
  dpkndls
  I recommend this post using Power Query from GilbertQ: https://gqbi.wordpress.com/2016/09/07/create-dynamic-periods-for-fiscal-or-calendar-dates-in-power-bi/
Anonymous
Not applicable
9 years ago
Hi GilesWalker, Thanks for all the Dax formulas.
I have created Datekey table and all the other coloums, and created relationship with my flattable in powr bi desktop. But, when I am am trying to show data week wise, using week coloumn it is showing blank in X-axis.
My report requirment to show last four week of sales using storewise and category wise.
Please advice me for better logic
- mpm000
  Frequent Visitor
  8 years ago
  This is a little out of scope here, but is there a way to create this table to dynamically update and add a new day? For example, if I use the caledar(date(),date()) I'm obviously closing it with a specific date. Any way to make it where it continues and does not have to be updated manually in the future?
- mpm000
  Frequent Visitor
  8 years ago
  This is a little out of scope here, but is there a way to create this table to dynamically update and add a new day? For example, if I use the caledar(date(),date()) I'm obviously closing it with a specific date. Any way to make it where it continues and does not have to be updated manually in the future?
  - wwhittenton
    Helper II
    8 years ago
    mpm000 Assuming you haven't already figured this out or found the answer in another thread, the following formula should auto-update based on the current date. (Note my time table starts Jan 2000.)
    
    DateKey = CALENDAR( DATE(2000,01,01) , DATE(YEAR(NOW()) , MONTH(NOW()) , DAY(NOW()) ))
david40ni
Helper I
8 years ago
I like your approach would this cover me for oct to october finicial year.

Also how would I filter based on last years then using this table in a measure I want to show last years previous sales?

markus_zhang

Advocate III

7 years ago

Thanks for this tip, very helpful!

I found that

Index = CALCULATE(COUNT(DateKey[Date]),ALL(DateKey[Date]),FILTER(DateKey,DateKey[Date]<=EARLIER(DateKey[Date])))

This calculated column will show #ERROR after I marked the [Date] column into the Date Table, so I changed the formula to:

Index = 
    -DATEDIFF(
        DateKey[Date],
        CALCULATE(
            MIN(DateKey[Date]),
            ALL(DateKey)
        ),
        DAY
    ) + 1

Forum Discussion

Related content

Creating Date Tables

Create new table

Lakehouse Table Generate Create Table

Creating my own Date Table

Create rows from 2 tables