Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am pulling some data from Salesforce into PowerBI.
Id | Name |
12 | ABC Motors |
13 | Tesla |
14 | Toyota |
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:
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.
Solved! Go to 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.
Hi,
You may download my PBI file from here.
Hope this helps.
Thats great! What is the right order of steps to replicate that?
Create a Query and plug in
You are welcome. If my reply helped, please mark it as Answer. Yes, that is the right order.
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?
By going to Modelling > New Table.
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?
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.
Ok. Thanks.
So how can I get your new measure to allow for creating charts. It is not allowing me to use it.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |