Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a DAX measure like Below:
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
Solved! Go to 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
)
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
)
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |