Forum Discussion
CALENDAR DATES GENERATION
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?
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] ) )RETURNADDCOLUMNS (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/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) )
3 Replies
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] ) )RETURNADDCOLUMNS (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/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) )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
