Identify New Business DAX

[Anonymous]

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

[Ashish_Mathur]

Hi @Anonymous,

What result are you expecting?  Show the exact result you want.

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

Thanks @Ashish_Mathur will do.

[Anonymous]

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

[Ashish_Mathur]

Hi,

I do not understand the problem.  Please be specific where the problem is and also show me your expected result.

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

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

[v-caliao-msft]

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

Reference
http://community.powerbi.com/t5/Desktop/Show-data-of-last-months-from-a-particular-month-filtering-with/m-p/197395

Regards,

Charlie Liao

[Anonymous]

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

[Anonymous]

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