Forum Discussion

14 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

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

         

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