Same Week last Year Flag

[Atleti]

Hey all,

Apologies in advance as I'm fairly new to more complex DAX functions.

I'm attempting to create a column that flags whether or not a product was being sold in the same week last year and if so, flag 'Y' and if not, flag 'N'. I have a date table as well as ISO weeks and years to work with along with the product name, just unsure on how to put this all together to make sure it's flagging by week specifically.

Thanks in advance!

[v-cgao-msft]

Hi @Atleti ，

Please create two calculated columns.

week_and_year = YEAR('Table'[Dates])+WEEKNUM('Table'[Dates],2)*10000

Column = 
VAR _value_1 = 'Table'[Brand]
VAR _value_2 =
    CALCULATE (
        MAX ( 'Table'[week_and_year] ),
        FILTER ( 'Table', 'Table'[Brand] = _value_1 )
    )
VAR _value_3 =
    CALCULATE (
        MIN ( 'Table'[week_and_year] ),
        FILTER ( 'Table', 'Table'[Brand] = _value_1 )
    )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Brand] = _value_1
            && _value_2 - _value_3 = 1, "Y",
        "N"
    )

The result should be like this.

Attach the PBIX file for reference. Hope it helps.

If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

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

[Atleti]

First, thanks so much for your response! 

I attempted to implement this solution, but received "N" for every single result, so it doesn't appear to be working. I'd love to include a more thorough file that includes an anonymized version of the data I'm working with, but I don't see a way to attach files here. Is there some way I can send it to you?

Thanks!

[v-cgao-msft]

Hi @Atleti ，

Please read this post.

How to provide sample data

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

[Atleti]

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

[v-cgao-msft]

Hi @Atleti ,

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

[Atleti]

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! 

[v-cgao-msft]

Hi @Atleti ,

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.

WEEKNUM function

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

[Atleti]

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?

[v-cgao-msft]

Hi @Atleti ,

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

[v-cgao-msft]

Hi @Atleti，

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

[Atleti]

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!

[truptis]

@Atleti -> Try using the below:

Logic steps:
1- create a column that would return your current year's week number (using WEEKNUM())
eg-WeekNum =WEEKNUM([Date])
2- Create a column called WeekYear = [WeekNum] * 10000 + [Year]
eg- 12022, 22022,32022... for 1st week of 2022, 2nd week of 2022, 3rd week of 2022 and so on respectively.
3- Create another column Week_Last_Year = [WeekYear] -1
eg- (if WeekYear is 22022 then Week_Last_year will be 22021)
4- Create a measure called TOTALSALES=sum(totalsales)
5- Create a measure for current week
Current_week = WEEKNUM(TODAY()) * 10000 + YEAR(TODAY())
6-Create measure for current week's sales

Current_Week_Sales= Calculate ([TotalSales],Filter(ALL(Calendartable), [WeekYear] = [Current_week ] ))

7. Create a measure called LastYear_SameWeekSales = Calculate ([TotalSales],Filter(ALL(Calendartable), [WeekLastYear] = [Current_Week] - 1))

Using this, you can compare your Sales at the same time this year and last year.
Please mark it as a solution if this helps you. Thanks.

Regards,
TruptiS

[Atleti]

I'm still having an issue with this one. I've tried a couple of the solutions offered here but nothing seems to be giving me the desired outcome. As a sample table below:

In this instance, I would be trying to flag down 'Brand C' because it was sold in the same week as last year. 

Brand	Dates
Brand A	1/1/2021
Brand B	1/2/2021
Brand C	1/3/2021
Brand D	2/1/2022
Brand C	1/3/2020
Brand B	1/1/2021

[VahidDM]

Hi @Atleti 

Check this link, might be helpful:

https://www.vahiddm.com/post/weekly-time-intelligence-dax

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[truptis]

Hi @Atleti ,
try using SAMEPERIODLASTYEAR & WEEKNUM()
Logic steps:
1- create a column that would return your current year's week number (using WEEKNUM())
eg-WeekNum =WEEKNUM([Date])
2- Create a column called WeekYear = [WeekNum] * 10000 + [Year]
eg- 12022, 22022,32022... for 1st week of 2022, 2nd week of 2022, 3rd week of 2022 and so on respectively.
3- Create another column Week_Last_Year = [WeekYear] -1
eg- (if WeekYear is 22022 then Week_Last_year will be 22021)
4- Create a measure called TOTALSALES=sum(totalsales)
5- Create a measure for current week
Current_week = WEEKNUM(TODAY()) * 10000 + YEAR(TODAY())
6-Create measure for current week's sales

Current_Week_Sales= Calculate ([TotalSales],Filter(ALL(Calendartable), [WeekYear] = [Current_week ] ))

7. Create a measure called LastYear_SameWeekSales = Calculate ([TotalSales],Filter(ALL(Calendartable), [WeekLastYear] = [Current_Week] - 1))

Using this, you can compare your Sales at the same time this year and last year.
Please mark it as a solution if this helps you. Thanks.

Regards,
TruptiS

[Atleti]

Hello and thanks for your reply!

To clarify, I am not looking for total sales. I am looking to see which products (by name) were being sold in the same week last year.

For example, if I know I sold Product A, B, and C this year this week, but I only sold Products A and B last year on the same week, I'd want to flag that product C was not being sold last year same week.