Forum Discussion

Mike282's avatar
Mike282
Helper 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. ...
Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

     

Mike282's avatar
Mike282
Helper III
7 years ago

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
    Helper III
    7 years ago

    Hi all,

     

    You can download my sample PBIX file here

     

    Any help would be greatly appreciated.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      You may download my PBI file from here.

      Hope this helps.

      • Mike282's avatar
        Mike282
        Helper III
        7 years ago

        Thanks Ashish_Mathur 

         

        I think it works. The problem is that I can verify the data in a table visual because it couldn't load. It's giving a error saying that there's not enough memory to complete this operation.

         

        The dataset I'm applying this measure to counts up to 183k reords over 3 years. Also it looks like when I Group the measure by month in a table it's giving really low numbers (like 23) for Cancelled but when I export the data and run a pivot I'm getting like over 500 plus cancelled subscriptions for the period of February when comparing with January data.

         

        Kind regards,

        Michael