Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am currently using the following DAX statement to create a Date Table:
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
I am looking to incorporate additional dimensions of week number, and week number and year. Does anyone have a suggestion how may include this information?
How can I incorporate this in the date hierarchy?
Thanks in Advance!
Solved! Go to Solution.
Hi @irnm8dn,
You could try this:
Date = ADDCOLUMNS ( CALENDAR (DATE(2016,1,1), DATE(2016,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "WeekNumber",WEEKNUM([Date]), "Year WeekNumber",FORMAT ( [Date], "YYYY" ) & "/Week" & WEEKNUM([Date]) )
Best regards,
Yuliana Gu
Hi @irnm8dn,
You could try this:
Date = ADDCOLUMNS ( CALENDAR (DATE(2016,1,1), DATE(2016,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "WeekNumber",WEEKNUM([Date]), "Year WeekNumber",FORMAT ( [Date], "YYYY" ) & "/Week" & WEEKNUM([Date]) )
Best regards,
Yuliana Gu
Hi irnm8dn,
I normally do the calendar table in M on Power Query. It's easier for me.
But maybe this thread may help you ...
https://community.powerbi.com/t5/Desktop/Need-help-creating-week-number/m-p/14204
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |