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,

 

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