Forum Discussion
Subscription Purchase reduction or cancellation DAX
Hi all,
I've got a question regarding what I can do in Power BI to determine subscription cancellation and module reduction.
UPDATE: You can download the sample PBIX file here from DropBox.
I've got a list of paid invoices with unique INIDs, Customer_IDs (where the same customer could have multiple subscription invoices) and a Created on Date field.
I need to identify month by month whether a customer has cancelled all their subscriptions entirely or reduced their subscription numbers by not having as much paid invoices for the next month. You can see in the pivot/matrix chart below a count of paid invoices grouped by customer and divided by Month.
What I want to do is potentially create a calculated column or measure to identify whether a Customer has Cancelled (if January numbers contain data but February does not contain data as in customer "CC") or If a customer has reduced the number of subscription invoices as in customer "AA" where January they had 12 subscriptions and in February they only had 5.
Any help would be greatly appreciated.
Kind regards,
Mike
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.
14 Replies
- Mike282
Helper III
Also to the same extent if January 2019 has no data and February 2019 has data then this is a new customer.
- Mike282
Helper III
- Ashish_Mathur
Super User