cancel
Showing results for
Did you mean:
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]),
)
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.

Petek

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

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.

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.

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
Member

## 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```