Forum Discussion
Helper IIISubscription 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.
Helper IIII tried the DAX and it doesn't seem to bring in the right numbers. It's alot lower than when I calculate the numbers on a spreadsheet. Just as an example I've attached the spreadsheet. If you look in the pivot table sheet in the spreadsheet, you'll see some formulas I use to calculate cancellation, reduction and new sales.
Kind regards,
Mike
Super UserHi,
My formula gets your desired result. 1 cancelled in Feb, i reduced cancellation in Feb and no new addition in Feb. Where is the problem?
- Mike282
Thanks Ashish_Mathur
Apologies it looks like it's working perfectly for the Invoice table! Could I ask another quick question. We realised that we need to run the same formula but based on a level down by a table called InvoiceItem table. The InvoiceItem table as a many to 1 relationship to the Invoice table by the InID. The InvoiceItem table contains a breakdown of the products within the one invoice located in the Invoice table.
I want to essentially do the same calculation but completely from the InvoiceItem table instead. You can see in the attached PBIX file I've created a second matrix table visual to the right and it's built completely off the InvoiceItem table. I've tried several variations of your formula but I can't seem to get it working for a distinct count of the InID in the InvoiceItem table.
- Ashish_Mathur
Hi,
You are welcome. Before i answer the next question, please mark as Answer the particular post of mine which answered your first question.
- Mike282
- Ashish_Mathur
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.
- Mike282
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