Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |