Date Table in Query Editor or In Data View

Power BI
1

Where is it good to create date table. i have seen many create date table in query mode or some in data view. which one is best and easy. would it impact if i create date table in query view?

2

@dipeshkumar30,

The short answer is to create it from a blank query. @Melissa’s video below walks through the best date table code and how best to create your date table from it:

  • Brian
1 Like
3

Best code here

1 Like
4

thanks a lot sir

5

Hello, @dipeshkumar30.

In general the best way to create a date table is using M code, you can follow the indications left by @BrianJ and @sam.mckay which are wonderful, however if you somehow feel the need to create a table used Dax find the code below.

Date Table

Date =
VAR FirstFiscalMonth = 7 -- First month of the fiscal year

VAR FirstDayOfWeek = 0 -- 0 = Sunday, 1 = Monday, ...

VAR FirstYear = -- Customizes the first year to use

YEAR ( MIN ( Sales[Order Date] ) )

RETURN

GENERATE (

FILTER (

CALENDARAUTO ( ) ,

YEAR ( [Date] ) > = FirstYear

) ,

VAR Yr = YEAR ( [Date] ) -- Year Number

VAR Mn = MONTH ( [Date] ) -- Month Number (1-12)

VAR Qr = QUARTER ( [Date] ) -- Quarter Number (1-4)

VAR MnQ = Mn - 3 * ( Qr - 1 ) -- Month in Quarter (1-3)

VAR Wd = WEEKDAY ( [Date] , 1 ) - 1 -- Weekday Number (0 = Sunday, 1 = Monday, ...)

VAR Fyr = -- Fiscal Year Number

Yr + 1 * ( FirstFiscalMonth > 1 && Mn > = FirstFiscalMonth )

VAR Fqr = -- Fiscal Quarter (string)

FORMAT ( EOMONTH ( [Date] , 1 - FirstFiscalMonth ) , "\QQ" )

RETURN ROW (

"Year" , DATE ( Yr , 12 , 31 ) ,

"Year Quarter" , FORMAT ( [Date] , "\QQ-YYYY" ) ,

"Year Quarter Date" , EOMONTH ( [Date] , 3 - MnQ ) ,

"Quarter" , FORMAT ( [Date] , "\QQ" ) ,

"Year Month" , EOMONTH ( [Date] , 0 ) ,

"Month" , DATE ( 1900 , MONTH ( [Date] ) , 1 ) ,

"Day of Week" , DATE ( 1900 , 1 , 7 + Wd + ( 7 * ( Wd < FirstDayOfWeek ) ) ) ,

"Fiscal Year" , DATE ( Fyr + ( FirstFiscalMonth = 1 ) , FirstFiscalMonth , 1 ) - 1 ,

"Fiscal Year Quarter" , "F" & Fqr & "-" & Fyr ,

"Fiscal Year Quarter Date" , EOMONTH ( [Date] , 3 - MnQ ) ,

"Fiscal Quarter" , "F" & Fqr

)

)

Please do not kill me for thinking that there is a case although there are few that using a date table in Dax is the best option.

Best regards.

Gifted

1 Like
6

@Gifted,

Alternate viewpoints and approaches always welcome here.

Yours is a perfectly acceptable approach, and one I used to use myself. However, @Melissa’s date table includes a number of features, which I think make it a much preferred alternative, including the ability to incorporate a holiday table, the ISAFTERTODAY function and most importantly, the use of offsets which are incredibly powerful and can dramatically simplify many time intelligence calculations.

For a full series on maximizing the features of this extended date table, check out this video series:

  • Brian