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 s...
  • v-frfei-msft's avatar
    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" )
                    )
                )
            )
        )