topic Re: DAX Week Table and Not by Day in DAX Commands and Tips

DAX Week Table and Not by Day

mike9999 — Wed, 17 May 2023 18:37:47 GMT

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 want the first day of the week, so I am attempting to filter down using [DayOfWeek] = 1 however I get an error "Cannot find table FullCalendar"

Desired output:

Year	Week Number	Year-Week	DayOfWeek	FirstDayOfWeek
2022	50	2022-50	1	12/5/22
2022	51	2022-51	1	12/12/22
2022	52	2022-52	1	12/19/22
2022	53	2022-53	1	12/26/22
2023	1	2023-1	1	1/2/23
2023	2	2023-2	1	1/9/23
2023	3	2023-3	1	1/16/23
2023	4	2023-4	1	1/23/23

Current DAX Table with error:

WeekTable = VAR FullCalendar = 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) ) RETURN CALCULATETABLE( SUMMARIZE( FullCalendar, [Week Number], [Year], [Year-Week], [FirstDayOfWeek], [DayOfWeek] ), FullCalendar[DayOfWeek] = 1 )

Re: DAX Week Table and Not by Day

Jihwan_Kim — Thu, 18 May 2023 03:27:27 GMT

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 )

Re: DAX Week Table and Not by Day

mike9999 — Thu, 18 May 2023 12:14:38 GMT

Amazing, 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.