Net Order Calculation

[ericci]

Hello,

I hope you all can help!

I have sales opportunity data pulled from Salesforce and am trying to calculate net orders by quarter for the current year. However, the problem I run into is when we have a customer order and cancellation in the same year. Any measures I create would only capture the cancellation becuase the signup and cancellation is one opportunity. Our data looks something like this:

ID      Name       Model      Stage Name        IsWon   Signup Date     Cancellation Date 

1       Cust. A    Model A       Closed Won       True         1/15/2020

2       Cust. B    Model B       Cancelled           False        1/15/2020         9/30/2020   

3       Cust. C    Model B       Closed Won        True        1/15/2020         

I would like to create a table visual that looks like the below, and captures sign-ups and cancellations

                   1Q   2Q   3Q   4Q  Total

Model A       1     0      0      0       1

Model B       2     0     -1      0       1

Total            3     0      -1     0       2

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ericci]

@Ashish_Mathur  Thank you for your response. Unfortunately, our company is still working with a prior version of PowerBI (October release), and I cannot open the file to verify the solution yet. Is there a workaround you are aware of that will allow me to open the file? I've also emailed my internal IT department for assistance.

[parry2k · Accepted solution]

@ericci solution is attached, you can take it from here and tweak as per your need.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ericci]

@parry2kThank you for your response. Unfortunately, our company is still working with a prior version of PowerBI (October release), and I cannot open the file to verify the solution yet. Is there a workaround you are aware of that will allow me to open the file? I've also emailed my internal IT department for assistance.