How to display increase sale, Decrease sale, New Sale in power BI

[Abrar786]

In the table above, I want to find the increase in sales based on the current year (CY) sales and categorize those sales according to the category name. However, I am not getting the correct total value for the increase, and I am also unsure if my DAX calculation is correct. Any help would be appreciated. 

Increase Sale =

CALCULATE (

    [CY Sale] - [LY Sale],

    FILTER (

        VALUES ( invoices_details[customer_no] ),

        [CY Sale] > 0

            && [LY Sale] > 0

            && [CY Sale] > [LY Sale]

    )

)

WHERE

CY Sale =

CALCULATE (

    SUM ( invoices_details[sale_cad] ),

    FILTER (

        VALUES ( invoices_details[customer_no] ),

        COUNTROWS (

            FILTER (

                'category',

                invoices_details[sale_cad] >= 'category'[category min value]

                    && invoices_details[sale_cad] < 'category'[category max value]

            )

        ) > 0

    )

)

LY Sale =

CALCULATE([CY Sale],SAMEPERIODLASTYEAR('Calendar'[Date]))

Modal

[Abrar786]

Hi @amitchandak ,

Thank you for the reply. My measures are working fine. I am using following measure 

Increase Sale =

CALCULATE (

    [CY Sale] - [LY Sale],

    FILTER (

        VALUES ( invoices_details[customer_no] ),

        [CY Sale] > 0

            && [LY Sale] > 0

            && [CY Sale] > [LY Sale]

    )

)

With this measure, the total increase in sales results is incorrect. However, if I use the same measure at the customer level, it works fine. The issue arises only when I apply this measure to the category level. You can see this in attached picture1.

[amitchandak · Accepted solution]

@Abrar786 , Try like

Increase Sale =
Sumx (FILTER (
VALUES ( invoices_details[customer_no] ),
[CY Sale] > 0
&& [LY Sale] > 0
&& [CY Sale] > [LY Sale]
),
[CY Sale] - [LY Sale]
)

or

Sumx (FILTER (
VALUES ( invoices_details[customer_no] ),
[CY Sale] > 0
&& [LY Sale] > 0
&& [CY Sale] > [LY Sale]
),
calculate([CY Sale] - [LY Sale])
)

[amitchandak]

@Abrar786 , hope measures working fine

Then create a measure like

Switch( True(),

[CY Sale] > [LY Sale], "Increase",

[CY Sale] < [LY Sale], "Decrease",

"Same"

)

Then do dynamic segmentation using an independent table, measures

refer example

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q