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
Super User
8 years ago

Hi Anonymous,

 

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Ashish_Mathur
    Super 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
        Super 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.