DAX Week Table and Not by Day

[mike9999]

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
    )

[Jihwan_Kim · Accepted solution]

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
)

[mike9999]

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.