cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Mike282
Helper III
Helper III

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.

 

Table.PNG

 

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.

 

Pivot.png

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

3 ACCEPTED SOLUTIONS

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.

Untitled.png 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Mike282
Helper III
Helper III

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

Hi all,

 

You can download my sample PBIX file here

 

Any help would be greatly appreciated.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

I 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.

 

Sample spreadsheet

 

Kind regards,

Mike

Hi,

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Invoice Item Visual.PNGInvoice Item.PNG

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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).

 

Reduction and Cancelation numbers.png

 

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

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.

Untitled.png 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

OMG that worked perfectly. Thank you Ashish.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

You are welcome.  Before i answer the next question, please mark as Answer the particular post of mine which answered your first question.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks @Ashish_Mathur 

 

Done! Much appreciated!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors