Flag New Clients using Opportunity Date field

[simonGIS]

I am pulling some data from Salesforce into PowerBI.

• I have an Account Table:

Id	Name
12	ABC Motors
13	Tesla
14	Toyota

• And a 1:M relationship setup with the Opportunity table:

AccountId	StageName	Amount	CloseDate
12	Closed Lost	5040	01/04/2018
12	Closed Lost	1750	27/06/2019
13	Closed Lost	3400	08/12/2018
13	Closed Won	100000	30/11/2018
13	Closed Won	1090	12/05/2019
14	Closed Lost	10720	16/03/2018
14	Closed Won	3410	20/05/2019

I believe a new customer can be defined as:

• If the minimum CloseDate of all Opportunity.StageName = ClosedWon, is within the last 3 months = True, otherwise False.
• In the above example:

Id	Name	New Customer
12	ABC Motors	False
13	Tesla	False
14	Toyota	True

I think it would make sense to calculate this in the Query Editor using Power Query M expression on the Account table to define a new field, but open to suggestions and alternative option using DAX in the Data View.

I am new to Power BI and still running through some online tutorials, but would really appreciate some pointers on how to achieve this.  

I saw some other posts talking about creating separate calendar tables, but I do not think I need to do this.

The end goal will be to leverage this value to show in charts to highlight the new vs existing clients we have by territory.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[simonGIS]

Thats great!  What is the right order of steps to replicate that?

Create a Query and plug in 

Calendar = CALENDAR(MIN(Opportunity[CloseDate]),MAX(Opportunity[CloseDate]))

Join the Calendar back to the Opps table?

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.  Yes, that is the right order.

[simonGIS]

Struggling a little to reverse engineer.

I understand the new measures ok.

But what are the specific steps to generate the Calendar table.

Where do I plug the following expression?

Calendar = CALENDAR(MIN(Opportunity[CloseDate]),MAX(Opportunity[CloseDate]))

New table from a DAX expression?

[Ashish_Mathur]

By going to Modelling > New Table.

[simonGIS]

Thanks.  Got it semi-working with my data.

The end goal will be to leverage this value to show in charts to highlight the new vs existing clients

e.g. Bar Chart of clients, stacked by existing/new

With your dashboard, I can't use the New Customer field for any of the visualisation filters or legends.

If I wanted to create some charts using the New Customer field, it appears to not like using the NewClient measure.  It works fine in the table as an additional column, but trying to use it to distinguish new clients in both a bar chart and a shape map, it fails to let me use it.

I have tried recreating the measures under the Account table, but it still won't let me use the New Customer measure in my visulisations.

Would a better approach be to create a new field in data view for the account table, and somehow create a confitional expression in a new column that uses logic similar to your previous formula?

I can create a new column and use this expression:

NewClient6mths =
IF(AND(DATEDIFF([Date of first win],TODAY(),MONTH)<=6,[Date of first win]<>BLANK()), "NEW", "NOT NEW")

Can you comment on this approach?

[Ashish_Mathur]

That would not be a good approach.  If you show New and Old via a calculated column formula (as opposed to a measure which is my existing solution), the formula will not recalculate when filter/slicers are changed.

[simonGIS]

Ok.  Thanks. 

So how can I get your new measure to allow for creating charts.  It is not allowing me to use it.

[Iamnvt · Accepted solution]

hi,

if you want to slice anything, you need to use it as Calculated Column:

New customers - Calculated Column = 
VAR
lastwindate = CALCULATE(LASTNONBLANK(Opportunity[CloseDate],0), FILTER(ALL(Opportunity), Opportunity[AccountId] = EARLIER(Opportunity[AccountId]) && Opportunity[StageName] = "Closed Won"))
RETURN
IF(OR(DATEDIFF(lastwindate, TODAY(),MONTH) >3, lastwindate = BLANK()), "FALSE", "TRUE")

if you don't want to slice, then Measure is fine:

New Customer - Measure = 
VAR
closestwindate3 = 
CALCULATE(LASTNONBLANK('Calendar'[Date],0), FILTER('Calendar',CALCULATE(SUM(Opportunity[Amount])>0) && FIRSTNONBLANK(Opportunity[StageName],0) = "Closed Won"))
VAR
result =
IF(OR(DATEDIFF(closestwindate3,TODAY(),MONTH) > 3, closestwindate3 = BLANK()), "FALSE", "TRUE")
return
result

here is the PBI file.

https://1drv.ms/u/s!Aps8poidQa5zk6puzgZlCXbVuvWU_w

[Ashish_Mathur]

Hi,

Is this the result you are expecting?  Check the revenue earned figure as well.

If yes, then share a larger dataset so that i can scheck the accuracy.  Also, share other columns that you have have such as territory which you would like to slice/filter your visual by.