Forum Discussion
mike9999
Advocate I
Advocate IDAX Week Table and Not by Day
Hi, I'm trying to make a DAX table that shows all of the weeks within a particular range (ex. Dec 1, 2022 to Dec 31, 2023). CALENDAR will have every single day of the range however, I simply wan...
Hi,
I am not sure if I understood your question correclty, but if you want to see the result of the above formula, please try something like below.
WeekTable = FILTER ( SUMMARIZE ( ADDCOLUMNS ( CALENDAR ( "2022/12/1", "2023/12/31" ), "Week Number", WEEKNUM ( [Date] ), "Year", YEAR ( [Date] ), "Year-Week", YEAR ( [Date] ) & "-" & WEEKNUM ( [Date] ), "FirstDayOfWeek", [Date], "DayOfWeek", WEEKDAY ( [Date], 2 ) ), [Week Number], [Year], [Year-Week], [FirstDayOfWeek], [DayOfWeek] ), [DayOfWeek] = 1 )
Jihwan_Kim
Super User
Super UserHi,
I am not sure if I understood your question correclty, but if you want to see the result of the above formula, please try something like below.
WeekTable =
FILTER (
SUMMARIZE (
ADDCOLUMNS (
CALENDAR ( "2022/12/1", "2023/12/31" ),
"Week Number", WEEKNUM ( [Date] ),
"Year", YEAR ( [Date] ),
"Year-Week",
YEAR ( [Date] ) & "-"
& WEEKNUM ( [Date] ),
"FirstDayOfWeek", [Date],
"DayOfWeek", WEEKDAY ( [Date], 2 )
),
[Week Number],
[Year],
[Year-Week],
[FirstDayOfWeek],
[DayOfWeek]
),
[DayOfWeek] = 1
)
mike9999Advocate I
Advocate IAmazing, thank you. If I compare the difference from what I did, I'm assuming the issue was that the CALCULATETABLE filter doesn't work for this purpose since the VAR variable is not yet a table. Keeping the solution as a simple FILTER/SUMMARIZE without the VAR as you have shown is more concise and gets the correct solution.