cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pvural Frequent Visitor
Frequent Visitor

Re: How do i create a date table ?

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

nchambe Regular Visitor
Regular Visitor

Re: How do i create a date table ?

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

GilesWalker Established Member
Established Member

Re: How do i create a date table ?

@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 Established Member
Established Member

Re: How do i create a date table ?

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

david40ni Regular Visitor
Regular Visitor

Re: How do i create a date table ?

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?

Highlighted

Re: How do i create a date table ?

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