Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to identify new business between certain months this year and certain months the previous year
for example
October 2016 to September 2017
+
October 2015 to September 2016
Example Data
https://drive.google.com/file/d/0B0ozcJ6ZI3zEM1NZWGhhcUgtbFU/view?usp=sharing
Any ideas how I may go about this...
Thanks
Hi @Anonymous,
What result are you expecting? Show the exact result you want.
Hi @Ashish_Mathur,
I am wanting to eventutally produce a table that shows
New Business Table
Business Name | Revenue
Mr Moyarti 100,000,000
I am guessing the way it needs to be done is producing two tables in dax one for this year and one for last year then comparing to see if the name is in last years if it is then it is not new business if it is not then it is.
Thanks
Chris
Hi @Anonymous,
Assuming your financial year is from October to September, the following calculated field formulas will work.
Date of first interaction with Company
=CALCULATE(MIN(Query1[Date]),DATESBETWEEN('Calendar'[Date],DATE(2014,1,1),MAX('Calendar'[Date])))
New Customer
=CALCULATE(COUNTROWS(Query1),FILTER(VALUES(Query1[TrueCompany]),[Date of first interaction with Company]>=MIN('Calendar'[Date])))
In the slicer, if i choose the financial year as 2016-2017 i.e. from October 1, 2016 to September 30, 2017 and set the filter condition of New Customer to "is not blank", then nothing will show up because there was no new customer in this period (if we see the date since October 1, 2014. If we change the financial year to 2015-2016, then 1 customer shows up.
The two formulas shown above can be merged into one if you want.
Download the file from here.
Hope this helps.
Hi @Ashish_Mathur,
It seems to count the same company more than once,
Due to it having multiple instances of the company in the transaction table.
How would I go about making it distinct?
Thanks
Hi,
That should not be happening. The VALUES() function screated a unique list of all companies. Check that there should not be any invisible charachters such as spaces before/after Company name. If that still does not help, then share the link from where i can download the file where you are facing this problem. Show me the exact problem in that file.
Thanks @Ashish_Mathur
Basically I am needing it to the same as the other tables by sumarising the amount of new business and how many interactions they have.
See attached...
https://drive.google.com/file/d/0B0ozcJ6ZI3zEM1NZWGhhcUgtbFU/view?usp=sharing
Hi,
I do not understand the problem. Please be specific where the problem is and also show me your expected result.
Hi,
I am wanting to create a summarized table showing
Number of New Business Number of interactions Revenue
Then a table showing
Number of Business That spent this Year and Last Year Number of interactions Revenue
and
Number of Lost Businesses Number of interactions Revenue
using the title slicer and month slicer.
Thanks
With the true company and new business in the same table it says new business is 166, however if you remove the true company field it says there are 1003
Something like
Table 6 = SUMMARIZE(Query1,Query1[TrueCompany],"New Business",CALCULATE(COUNTROWS(Query1),FILTER(VALUES(Query1[TrueCompany]),[Date of first interaction with Company]>=MIN('Calendar$'[Date])),Query1[ttlname] = "Barrow Advertiser"),"Number of Weeks",CALCULATE(DISTINCTCOUNT(Query1[FinWeek of Year]),FILTER(VALUES(Query1[TrueCompany]),[Date of first interaction with Company]>=MIN('Calendar$'[Date])),Query1[ttlname] = "Barrow Advertiser"))
@Anonymous,
We can use a meaure to count the news on a period.
Measure = CALCULATE(COUNTA(Query1[mediatype]),FILTER(ALL(Query1),Query1[Date]>=STARTDATE&&Query1[Date]<=ENDDATE))
Regards,
Charlie Liao
Hi @v-caliao-msft,
Thanks for that,
There needs to be some comparisons between the True COmpany field though so I need to say if this company was found last year it is not new business?
Thanks
Chris
I came across this pattern online
[New Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "PreviousSales", CALCULATE ( COUNTROWS ( Sales ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( 'Date'[FullDate] ) ) ) ), [PreviousSales] = 0 ) )
How would I go about amending it to say if customer didnt buy oct15-sept 16 but bought oct16 - sep17 it is a new customer..
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |