Forum Discussion

sbowles's avatar
sbowles
Regular Visitor
10 years ago

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..    e...
GilesWalker's avatar
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

PeterBI's avatar
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's avatar
    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's avatar
      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's avatar
    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's avatar
      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