DAX Calculating on Individual Level

[tc_WII]

Hello,

I have a DAX measure like Below:

VAR _CY     = CALCULATE([Revenue], dimCustomer[BusinessType] = "Lost")

    VAR _PY     = CALCULATE([Revenue], DATEADD(dateTable[Date],-1,YEAR),dimCustomer[BusinessType]="Lost")

    VAR Result  = IF(_PY > _CY, _PY - _CY)

    RETURN    Result

I am having one issue here. When I have a table with Month and customerNo. It gives me correct result for each customer. But when I only need the whole month overview, it is first calculating  the whole month CY then Whole Month PY, then if PY is smaller, it comes blank. But I want to calculate on each customer and add those. 

lets say, i have customerNo 1, 2, 3.

CustomerNo	CY	PY	CYvsPY
1	1000	1200	200
2	10000	8000	"Blank"
3	500	600	100
Total	11500	9800	(Showing Blank but I need 300)

when I see all customer result, it first calculates sum of CY that is 11500 and Sum PY 9800. And it gives CYvsPY 0.

but I want it to calculate on individual level. so that on the total card it should give me 300

I hope I could clarify the question. Please help me out

[johnt75 · Accepted solution]

Try

CY vs PY =
SUMX (
    VALUES ( 'dimCustomer'[Customer ID] ),
    VAR _CY =
        CALCULATE ( [Revenue], dimCustomer[BusinessType] = "Lost" )
    VAR _PY =
        CALCULATE (
            [Revenue],
            DATEADD ( dateTable[Date], -1, YEAR ),
            dimCustomer[BusinessType] = "Lost"
        )
    VAR Result =
        IF ( _PY > _CY, _PY - _CY )
    RETURN
        Result
)

[tc_WII]

@johnt75 

Thank you for your solution. This worked perfectly fine. Just for the sake of knowledge, do you know why my below solution did not work? I only defined these VAR before SUMX.

 VAR _CY     = CALCULATE([Revenue], dimCustomer[BusinessType] = "Lost")

    VAR _PY     = CALCULATE([Revenue], DATEADD(dateTable[Date],-1,YEAR),dimCustomer[BusinessType]="Lost")

    VAR Result  = IF(_PY > _CY, _PY - _CY)

    RETURN    SUMX(VALUES(dimCustomer[Customer No]),Result)

[johnt75]

Variables in DAX aren't really variables, they're constants. They're only calculated once, when they are defined, no matter how many times they are used. So because you declared the variables outside the SUMX they were only calculated once, at the grand total level, and then these total values were used for each iteration over the customers.