Same Week last Year Flag

Hi Anonymous，

Filtering on different years may return different results, and calculated columns are not suitable for handling this. Because the calculated column is recalculated when the data is refreshed. Please consider using measures and filters to handle this.
Please create a measure.

Total = 
var _value_1 = MAX('Sheet1'[BRAND_VALUE])
var _value_2 = MAX('Sheet1'[week_and_year])
var _value_3 = MAX('Sheet1'[week_and_year]) - 1
var _value_5 = CALCULATE(COUNT('Sheet1'[BRAND_VALUE]),FILTER(ALL('Sheet1'),'Sheet1'[BRAND_VALUE]=_value_1),FILTER(ALL('Sheet1'),Sheet1[week_and_year]=_value_2))
var _value_6 = CALCULATE(COUNT('Sheet1'[BRAND_VALUE]),FILTER(ALL('Sheet1'),'Sheet1'[BRAND_VALUE]=_value_1),FILTER(ALL('Sheet1'),Sheet1[week_and_year]=_value_3))
RETURN
SWITCH(
    TRUE(),
    _value_5 <> BLANK() && _value_6 <> 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

Our calendars appear to be the same.

One note though - ideally this filter would be able to show Y or N for one year ago only. For instance, if 2022 and 2021 both have a brand, they should both say Y, but if 2022 has a brand that 2020 does not and you filter to 2021 vs 2020, that brand should not show (as the comparison would be looking to 2021 vs 2020).

I understand this is pretty complex and I'm not certain if it's achievable in just one column. If there is a better workaround to this OR if multiple columns/measures may be needed for each yearly comparison, that would be okay.

Thank you!

Some good news...

I manipulated the code you provided in your most recent comment to be:

week_and_year = YEAR('FV_SALES_BASE'[Testdate])+WEEKNUM('FV_SALES_BASE'[Testdate],1)*10000

TestColumn = 
var _value_1 = 'FV_SALES_BASE'[BRAND_VALUE]
var _value_2 = 'FV_SALES_BASE'[week_and_year]
var _value_3 = 'FV_SALES_BASE'[week_and_year] - 1
var _value_5 = CALCULATE(COUNT('FV_SALES_BASE'[BRAND_VALUE]),FILTER('FV_SALES_BASE','FV_SALES_BASE'[BRAND_VALUE]=_value_1),FILTER('FV_SALES_BASE',FV_SALES_BASE[week_and_year]=_value_2))
var _value_6 = CALCULATE(COUNT('FV_SALES_BASE'[BRAND_VALUE]),FILTER('FV_SALES_BASE','FV_SALES_BASE'[BRAND_VALUE]=_value_1),FILTER('FV_SALES_BASE',FV_SALES_BASE[week_and_year]=_value_3))
RETURN
SWITCH(
    TRUE(),
    (_value_5 <> BLANK() && _value_6 <> BLANK())
  ,"Y",
    "N"
)

I then added an additional date column that filtered to only the past 2 years:

Testdate = Dateadd('FV_SALES_BASE'[DT],2,year)

By doing this, I was able to achieve accurate 'Y' and 'N' for the year of 2022. In theory, I could do this for every year but it would add an additional 12 columns total to my table I believe (as I think I would need these 3 columns individually for each year, going back to 2019.) 

This is a potential solution, but far from ideal. Are there possible workarounds to this problem that I could utilize?

Hi Anonymous ,

Did my answer solve the problem? If yes, please consider marking it as a 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 me know.

Best Regards,
Community Support Team_Gao