Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Identify New Business DAX

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 201...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    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.