Forum Discussion

Mike282's avatar
Mike282
Icon for Helper III rankHelper III
7 years ago
Solved

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

  • Also to the same extent if January 2019 has no data and February 2019 has data then this is a new customer.

    • Mike282's avatar
      Mike282
      Icon for Helper III rankHelper III

      Hi all,

       

      You can download my sample PBIX file here

       

      Any help would be greatly appreciated.