Weekly calendar date filter for the year starting on a Thursday

[Anonymous]

Hi Team,

Can anyone share a PBIX file which shows a weekly calendar starting on a Thursday from 2022-2024?

I also want to be able to filter data with a weekly slicer for the period 2022 - 2024

Thanks in advance

11-30

12-7

12-14

12-21

12-28

1-4

1-11

1-18

1-25

2-1

2-8

2-15

2-22

[danextian · Accepted solution]

Hi @Anonymous

Formula below is a sample DAX calendar table. This is a calculated table.

Calendar =
VAR __MIN =
    DATE ( 2022, 1, 1 )
VAR __MAX =
    DATE ( 2024, 12, 31 )
VAR __BASE =
    CALENDAR ( __MIN, __MAX )
VAR __RESULT =
    ADDCOLUMNS (
        ADDCOLUMNS (
            __BASE,
            "Year", YEAR ( [Date] ),
            "Month Long", FORMAT ( [Date], "mmmm" ),
            "Month Short", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Month and Year", FORMAT ( [Date], "mmm-yy" ),
            "YYYYMM", FORMAT ( [Date], "YYYYMM" ),
            "Quarter", "Q" & QUARTER ( [Date] ),
            "Day of Week Long", FORMAT ( [Date], "dddd" ),
            "Day of Week Short", FORMAT ( [Date], "ddd" ),
            "Day Sort", WEEKDAY ( [Date], 1 ),
            "Week of Year", WEEKNUM ( [Date] ),
            "Week Ending Saturday",
                VAR __DAY =
                    WEEKDAY ( [Date] ) - 7
                RETURN
                    [Date] - __DAY,
            "Week Ending Wednesday",
                VAR __DAY =
                    WEEKDAY ( [Date] ) - 4
                RETURN
                    [Date] - __DAY
        ),
        "Week Starting Thursday", [Week Ending Wednesday] - 6
    )
RETURN
    __RESULT

[danextian · Accepted solution]

Hi @Anonymous

Formula below is a sample DAX calendar table. This is a calculated table.

Calendar =
VAR __MIN =
    DATE ( 2022, 1, 1 )
VAR __MAX =
    DATE ( 2024, 12, 31 )
VAR __BASE =
    CALENDAR ( __MIN, __MAX )
VAR __RESULT =
    ADDCOLUMNS (
        ADDCOLUMNS (
            __BASE,
            "Year", YEAR ( [Date] ),
            "Month Long", FORMAT ( [Date], "mmmm" ),
            "Month Short", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Month and Year", FORMAT ( [Date], "mmm-yy" ),
            "YYYYMM", FORMAT ( [Date], "YYYYMM" ),
            "Quarter", "Q" & QUARTER ( [Date] ),
            "Day of Week Long", FORMAT ( [Date], "dddd" ),
            "Day of Week Short", FORMAT ( [Date], "ddd" ),
            "Day Sort", WEEKDAY ( [Date], 1 ),
            "Week of Year", WEEKNUM ( [Date] ),
            "Week Ending Saturday",
                VAR __DAY =
                    WEEKDAY ( [Date] ) - 7
                RETURN
                    [Date] - __DAY,
            "Week Ending Wednesday",
                VAR __DAY =
                    WEEKDAY ( [Date] ) - 4
                RETURN
                    [Date] - __DAY
        ),
        "Week Starting Thursday", [Week Ending Wednesday] - 6
    )
RETURN
    __RESULT

[Anonymous · Accepted solution]

@danextian  thank you for your help . This is very helpful

[danextian]

Welcome. Please accept my response as solution.