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.
I have the following piece of code to try and calculate the number of members that have lost more than 20% in turnover the last month.
@Anonymous , Try like
last month.
VAR __PREV_MONTH = CALCULATE([Turnover Sum], DATEADD('Date'[Date], -1, MONTH))
VAR MOM_RESULT = DIVIDE([Turnover Sum] - __PREV_MONTH, __PREV_MONTH)
VAR RESULT = Countx(FILTER('Purchase Joined',VALUES('Purchase Joined'[Member Key]), MOM_RESULT < -0.2),MOM_RESULT)
RETURN
RESULT
Doesn't work. Your query does not work, but I changed it to:
MdxScript(Model) (163, 90) Calculation error in measure 'Purchase Joined'[Members at risk MoM]: The function COUNTX cannot work with values of type Boolean.
@Anonymous , My Mistake
VAR __PREV_MONTH = CALCULATE([Turnover Sum], DATEADD('Date'[Date], -1, MONTH))
VAR MOM_RESULT = DIVIDE([Turnover Sum] - __PREV_MONTH, __PREV_MONTH)
VAR RESULT = Countx(FILTER(VALUES('Purchase Joined'[Member Key]), MOM_RESULT < -0.2),MOM_RESULT)
RETURN
RESULT
I get the same result as previously. It won't show the totals due to the total not being less than -0.2. If I "flip" the greater than sign (MOM_RESULT > -0.2), it shows the total. That is due to the total being 28.36%, which is larger than -20%. So your query still evaluates the code on a row-by-row basis.
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 |
---|---|
107 | |
106 | |
86 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |