Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Same Week last Year Flag

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ，

 

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ，

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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