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

[venug]

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

[v-frfei-msft · Accepted solution]

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]

@v-frfei-msft 

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

Regards

Venu.

[venug]

@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]

Hi Everybody,

Can you  please help on this. Thanks in advance.

Regards

Venu