Forum Discussion
Same Week last Year Flag
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_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
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)*10000TestColumn =
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，
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_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
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