Forum Discussion

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
7 years ago
Hi,

You may download my PBI file from here.

Hope this helps.
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

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

Helper III

7 years ago

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.