Forum Discussion

venug's avatar
venug
Icon for Helper II rankHelper II
6 years ago
Solved

DAX - Define Store Type ( multiple may be req-if conditions)

Hi Everybody,

 

I want to define store types with my data.

 

oct'18 > 0 sales & oct'19 > 0 sales & same store should repeat in both months = RS

oct'18 > 0 sales & oct'19 < 0 sales & same store should repeat in both months = LS

oct'18 < 0 sales & oct'19 > 0 sales & same store should repeat in both months = ES

 oct'19 > 0 sales  = NS

 

i want to achieve new column using DAX qurie. output is also provided in excel sheet.

 

anybody can help on this,

 

Thanks in advance.

 

Regards

Venu

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi venug ,

     

    Please create a calculated column as below.

    Column = 
VAR code = 'Table'[Store Code]
VAR oct18 =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2018 )
    )
VAR oct19 =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2019 )
    )
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2018 )
    )
RETURN
    IF (
        YEAR ( 'Table'[Date] ) > 2018,
        IF (
            oct18 > 0
                && oct19 > 0,
            "RS",
            IF (
                oct18 > 0
                    && oct19 <= 0,
                "LS",
                IF (
                    c <> BLANK ()
                        && oct18 <= 0
                        && oct19 > 0,
                    "ES",
                    IF ( oct19 > 0 && ISBLANK ( c ), "NS" )
                )
            )
        )
    )

     

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi venug ,

     

    Please create a calculated column as below.

    Column = 
VAR code = 'Table'[Store Code]
VAR oct18 =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2018 )
    )
VAR oct19 =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2019 )
    )
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2018 )
    )
RETURN
    IF (
        YEAR ( 'Table'[Date] ) > 2018,
        IF (
            oct18 > 0
                && oct19 > 0,
            "RS",
            IF (
                oct18 > 0
                    && oct19 <= 0,
                "LS",
                IF (
                    c <> BLANK ()
                        && oct18 <= 0
                        && oct19 > 0,
                    "ES",
                    IF ( oct19 > 0 && ISBLANK ( c ), "NS" )
                )
            )
        )
    )

     

    • venug's avatar
      venug
      Icon for Helper II rankHelper II
      6 years ago

      v-frfei-msft 

       

      Thanks a lot for provide solution. It's really helpful to me.

      Regards

      Venu.

      • venug's avatar
        venug
        Icon for Helper II rankHelper II
        6 years ago

        v-frfei-msft 

         

        I try to define store for 2018 also,

         

        The condition is :

         

        oct'18 > 0 sales  = RS

        oct'18 > 0 sales & oct'19 < 0 sales & same store should repeat in current months = LS

         

        i have prepared code for this requirement, but it is executed for only one month either 2018 or 2019.

         

        Column =
        VAR code = 'Table'[Store Code]
        VAR oct18 =
        CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2018 )
        )
        VAR oct19 =
        CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2019 )
        )
        VAR c =
        CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2018 )
        )
        VAR c1 =
        CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[Store Code] = code && YEAR ( 'Table'[Date] ) = 2019 )
        )
        RETURN
        IF (
        'Table'[Date].[Year] = 2018,
        IF (
        oct18 > 0
        && c1 <> BLANK (),
        "RS",
        IF (
        oct18 > 0
        && ISBLANK ( c1 ),
        "LS",
        IF (
        YEAR ( 'Table'[Date] ) > 2018,
        IF (
        oct18 > 0
        && oct19 > 0,
        "RS",
        IF (
        oct18 > 0
        && oct19 <= 0,
        "LS",
        IF (
        c <> BLANK ()
        && oct18 <= 0
        && oct19 > 0,
        "ES",
        IF ( oct19 > 0 && ISBLANK ( c ), "NS" )
        )
        )
        )
        )
        )
        )
        )

         

  • venug's avatar
    venug
    Icon for Helper II rankHelper II
    6 years ago

    Hi Everybody,

     

    Can you  please help on this. Thanks in advance.

     

    Regards

    Venu