Forum Discussion
Same Week last Year Flag
I've attached two columns of anonymized data from the project I'm working on. There are several repeats in dates/brands, but this is because there is an additional column in my actual file that includes all of the different partners/stores we deal with.
Let me know if I can provide anything else and thanks again!
Link: https://drive.google.com/file/d/1YmP5ETQ4uMcwVMCTHKzV-F3Nsc1qazpZ/view?usp=sharing
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
I've tested this solution both in my original dataset and the test dataset I've provided. In both, I found some brands missing where they should be included(or brands included where they should be missing). I'll list an example picture below.
Also, as I said before the duplicates exist because in the table I'm using, there is an additional "Retailer" column which lists out the different retailers these brands are sold in.
In this example below, it mentions 'Y' in the 62021 column for brand 5 existing last year, but brand 5 was not sold in 62020. I'm wondering if possibly weeks are being skewed slightly by years that count 53 weeks instead of 52? Once again, thank you so much for your assistance in this solution!
Hi Anonymous ,
The reason is that there is a matching record in the sixth week of 2022.
I noticed that you mentioned the date using ISO week, The week containing the first Thursday of the year is the first week of the year and is numbered as week.
1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.
Is it the problem here? Please check if my calendar is the same as the one you want.
There is also how to deal with the situation that there is no record in the sixth week of 2020, but there is a record in the sixth week of 2021 and the sixth week of 2022.Best Regards,
Community Support Team_GaoIf 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!