Forum Discussion
Subscription Purchase reduction or cancellation DAX
Hi,
I see no difference between the Invoice and InvoiceItem table. WIth the relationship established correctly, you just have to edit all my measures to refer to the Invoiceitem table (rather than the Invoice Table).
Please try that yourself.
Hi,
Try this measure
=SUMX(FILTER(SUMMARIZE(VALUES(Invoice[Customer_ID]),[Customer_ID],"ABCD",[Subscriptions],"EFGH",[Subscriptions in previous month]),[ABCD]>0&&[ABCD]<[EFGH]),[EFGH]-[ABCD])
Hope this helps.
Thanks Ashish_Mathur you've been really helpful.
One more question, promise it'll be the last one. Is there anyway to get the difference of for example the reduction of subscription numbers between months as well as if they've canceled, what is the number of subscriptions canceled between months. See screenshot below for further clarification. Likewise I would also need to get the cancelation subscription numbers (I'd assume it'll be similarly calculated).
I've tried using a IF statement to bring in a MOM Variance if "Reduced is greater than 0
Measure for MOM Variance
Subscription MOM Variance = [Subscriptions]-[Subscriptions in previous month]
Measure for attempting to calculate the difference between subscription numbers between 2 months if reduced
Reduced Subscription Numbers = IF([Reduced]>0,[Subscription MOM Variance],"0")
This works when I select AA's reduction value in the matrix table and it'll give me a difference but when I try to plot it into a bar chart visual it just defaults to the standard MOM variance
The problem with the standard calculation for MOM variance is that it's just subtracting the difference between two months without factoring the movement (e.g. new subscriptions is a positive movement, whilst reductions and cancelations are a negative movement and the sum of positive and negative movement values give the overall MOM variance that the MOM variance measure calculates).
Kind regards,
Mike
Hi,
Try this measure
=SUMX(FILTER(SUMMARIZE(VALUES(Invoice[Customer_ID]),[Customer_ID],"ABCD",[Subscriptions],"EFGH",[Subscriptions in previous month]),[ABCD]>0&&[ABCD]<[EFGH]),[EFGH]-[ABCD])
Hope this helps.
You are welcome.