Forum Discussion

tc_WII's avatar
tc_WII
Frequent Visitor
3 years ago
Solved

DAX Calculating on Individual Level

Hello,   I have a DAX measure like Below:   VAR _CY     = CALCULATE([Revenue], dimCustomer[BusinessType] = "Lost")     VAR _PY     = CALCULATE([Revenue], DATEADD(dateTable[Date],-1,YEAR),dimCus...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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
)
johnt75's avatar
johnt75
Super User
3 years ago

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's avatar
    tc_WII
    Frequent Visitor
    3 years ago

    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's avatar
      johnt75
      Super User
      3 years ago

      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.