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),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.

 

CustomerNoCYPYCYvsPY
110001200200
2100008000"Blank"
3500600100
Total115009800(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

 
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
)

3 Replies

  • 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.