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

 

 

 

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