Forum Discussion
Asdd additional columns to calender
Hi Experts
how would you add the following columns to the DAX as shown below, need to get a complete calender .
1. Quarter&Year i.e Q3 2018
2. DaysinWeek i.e Monday = 1, Tuesday =2 etc..
3. WeekEnding
DimDate =
ADDCOLUMNS (
CALENDAR ( "01-01-2010", "31-12-2030"),
"Year", YEAR ( [Date] ),
"Quarter", CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 )),
"Quarter&Year", FORMAT(
"Month", MONTH ( [Date] ),
"SortMonthName", FORMAT ( [Date], "MMM" ),
"WeekNumber", WEEKNUM ( [Date] ),
"Weekday", WEEKDAY ( [Date] )- 1,
"Day", DAY ( [Date] ),
"DayName", FORMAT ( [Date], "DDDD" ),
"Mth&Yr", FORMAT ([Date], "MMMM YYYY"),
"SortMonth&Year", FORMAT([Date], "MMM" & " " & "YY"),
"Sort", FORMAT([Date], "YYYYMM"))Anonymous Here you go...
TestDimDate = ADDCOLUMNS ( CALENDAR ( "01-01-2010", "31-12-2030"), "Year", YEAR ( [Date] ), "Quarter", CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 )), "Quarter&Year", CONCATENATE(CONCATENATE ( CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 )), " "),YEAR([Date])), "Month", MONTH ( [Date] ), "SortMonthName", FORMAT ( [Date], "MMM" ), "WeekNumber", WEEKNUM ( [Date] ), "Weekday", WEEKDAY ( [Date] )- 1, "DayInWeek",WEEKDAY([Date],2), "WeekEnding",([Date]+7)-WEEKDAY([Date],2), "Day", DAY ( [Date] ), "DayName", FORMAT ( [Date], "DDDD" ), "Mth&Yr", FORMAT ([Date], "MMMM YYYY"), "SortMonth&Year", FORMAT([Date], "MMM" & " " & "YY"), "Sort", FORMAT([Date], "YYYYMM"))
3 Replies
- PattemManohar
Community Champion
Anonymous Here you go...
TestDimDate = ADDCOLUMNS ( CALENDAR ( "01-01-2010", "31-12-2030"), "Year", YEAR ( [Date] ), "Quarter", CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 )), "Quarter&Year", CONCATENATE(CONCATENATE ( CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 )), " "),YEAR([Date])), "Month", MONTH ( [Date] ), "SortMonthName", FORMAT ( [Date], "MMM" ), "WeekNumber", WEEKNUM ( [Date] ), "Weekday", WEEKDAY ( [Date] )- 1, "DayInWeek",WEEKDAY([Date],2), "WeekEnding",([Date]+7)-WEEKDAY([Date],2), "Day", DAY ( [Date] ), "DayName", FORMAT ( [Date], "DDDD" ), "Mth&Yr", FORMAT ([Date], "MMMM YYYY"), "SortMonth&Year", FORMAT([Date], "MMM" & " " & "YY"), "Sort", FORMAT([Date], "YYYYMM"))Thank you so much for the solution! I work for Gradesfixer and I'm only one person, who know something in this field. So, this is great that I can find solutions on various forums!
