CALENDAR DATES GENERATION

[Likando_Luywa]

table name : AW_Fact_Sales

field NAME: OrderDate

The first orderdate is 15 – October 2001 

The last orderdate is 29 – Nov 2019.

WANT TO CREATE A DATE FIELD IN MY CALEDER TABLE USING THE ABOVE VALUES AS MY STARTDATE AND ENDDATE

The below fields are in calendar table and they are generating dates of 1900

Date5 =

CALENDAR(FIRSTDATE(AW_Fact_Sales[OrderDate].[Date]),LASTDATE(AW_Fact_Sales[OrderDate].[Date]))

DATE 2 CALENDAR(FIRSTDATE(AW_Fact_Sales[OrderDate]),LASTDATE(AW_Fact_Sales[OrderDate]))

Even this field below is generating dates of 1900.

date4 = CALENDAR(15/10/2001,29/11/2019)

.WHY? 

[YukiK · Accepted solution]

You can create a calendar table using DAX code like the following:

Date =

VAR MinYear = YEAR ( MIN ( Sales[Order Date] ) )

VAR MaxYear = YEAR ( MAX ( Sales[Order Date] ) )

RETURN

ADDCOLUMNS (

    FILTER (

        CALENDARAUTO( ),

        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

    ),

    "Calendar Year", "CY " & YEAR ( [Date] ),

    "Month Name", FORMAT ( [Date], "mmmm" ),

    "Month Number", MONTH ( [Date] )

)

More information is on the sqlbi website: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[mahoneypat]

This article/video explains how to do this in the query editor or with DAX.  And it creates columns that do not need Sort By Columns (e.g., Month, YearMonth).

No Sort Date Tables! – Hoosier BI

Pat

[Fowmy · Accepted solution]

@Likando_Luywa 

Specify the start and end date and ensure the dates table has data for the whole year. You use below code for your Dates table creation,

Dates = 
VAR StartYear = YEAR( MIN(Orders[Order Date]) )
VAR EndYear = YEAR( MAX(Orders[Order Date]) )
VAR DatesColumn = CALENDAR( DATE(StartYear , 1 , 1), DATE(EndYear, 12 , 31))
RETURN
ADDCOLUMNS(
    DatesColumn,
    "Month No" , MONTH([Date]),
    "Month Name" , FORMAT( [Date] , "Mmmm" ),
    "Year" , YEAR([Date]),
    "Month Year No" , (YEAR([Date]) * 100) + MONTH([Date]),
    "Month Year" , FORMAT( [Date] , "Mmm yyyy"),
    "Quarter" , QUARTER([Date]),
    "Qtr Name" , FORMAT( [Date] , "\QQ"),
    "Week Day" , WEEKDAY([Date],2),
    "Week" , FORMAT( [Date] , "Dddd" ),
    "Week No" , WEEKNUM([Date],2),
    "Week Num" , "WK - " & WEEKNUM([Date],2)  
)

[YukiK · Accepted solution]

You can create a calendar table using DAX code like the following:

Date =

VAR MinYear = YEAR ( MIN ( Sales[Order Date] ) )

VAR MaxYear = YEAR ( MAX ( Sales[Order Date] ) )

RETURN

ADDCOLUMNS (

    FILTER (

        CALENDARAUTO( ),

        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

    ),

    "Calendar Year", "CY " & YEAR ( [Date] ),

    "Month Name", FORMAT ( [Date], "mmmm" ),

    "Month Number", MONTH ( [Date] )

)

More information is on the sqlbi website: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/