Same Week last Year Flag

Hi Anonymous ,

I modified the expression, please help me to check if they work correctly.

week_and_year = YEAR('Sheet1'[DT])+WEEKNUM('Sheet1'[DT],21)*10000

Column = 
var _value_1 = 'Sheet1'[BRAND_VALUE]
var _value_2 = 'Sheet1'[week_and_year]
var _value_3 = 'Sheet1'[week_and_year] - 1
var _value_4 = 'Sheet1'[week_and_year] + 1
var _value_5 = CALCULATE(COUNT('Sheet1'[BRAND_VALUE]),FILTER('Sheet1','Sheet1'[BRAND_VALUE]=_value_1),FILTER('Sheet1',Sheet1[week_and_year]=_value_2))
var _value_6 = CALCULATE(COUNT('Sheet1'[BRAND_VALUE]),FILTER('Sheet1','Sheet1'[BRAND_VALUE]=_value_1),FILTER('Sheet1',Sheet1[week_and_year]=_value_3))
var _value_7 = CALCULATE(COUNT('Sheet1'[BRAND_VALUE]),FILTER('Sheet1','Sheet1'[BRAND_VALUE]=_value_1),FILTER('Sheet1',Sheet1[week_and_year]=_value_4))
RETURN
SWITCH(
    TRUE(),
    (_value_5 <> BLANK() && _value_6 <> BLANK())
    ||
    (_value_5 <> BLANK() && _value_7 <> BLANK()),"Y",
    "N"
)

But the table has a lot of duplicate data, the same data only needs to output one result, it is recommended to use the following method, which will save a lot of memory.

First, we need a calendar table with three fields for date, year, and week. And create a relationship between sheet1 tables

Then create a new calculated table.

Table = SUMMARIZE('Sheet1','Sheet1'[BRAND_VALUE],'Date'[Year],'Date'[Week])

Please create a calculated column next.

Column = 
VAR _brand_value = 'Table'[BRAND_VALUE]
VAR _year = 'Table'[Year]
VAR _year_1 = 'Table'[Year] - 1
VAR _year_2 = 'Table'[Year] + 1
VAR _week = 'Table'[Week]
VAR _value_1 =
    CALCULATE (
        COUNT ( 'Table'[BRAND_VALUE] ),
        FILTER (
            'Table',
            'Table'[BRAND_VALUE] = _brand_value
                && 'Table'[Year] = _year
                && 'Table'[Week] = _week
        )
    )
VAR _value_2 =
    CALCULATE (
        COUNT ( 'Table'[BRAND_VALUE] ),
        FILTER (
            'Table',
            'Table'[BRAND_VALUE] = _brand_value
                && 'Table'[Year] = _year_1
                && 'Table'[Week] = _week
        )
    )
VAR _value_3 =
    CALCULATE (
        COUNT ( 'Table'[BRAND_VALUE] ),
        FILTER (
            'Table',
            'Table'[BRAND_VALUE] = _brand_value
                && 'Table'[Year] = _year_2
                && 'Table'[Week] = _week
        )
    )
RETURN
    SWITCH (
        TRUE (),
        (_value_1 <> BLANK()&& _value_2 <> BLANK())
        ||
        (_value_1 <> BLANK()&& _value_3 <> BLANK()), "Y",
        "N"
    )

Attach the PBIX file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data