Forum Discussion

ani_informa's avatar
ani_informa
Helper III
3 years ago

Calculate First Acquisition

Hi 

I have a requirement wherein I want to write a DAX that can calculate the New Acquisition of customers. For example, from the sample data below, steve has enrolled first on Feb 14, 2022 and though he has enrolled for other programs in later time, Feb 14 is the first acquisition date for Steve. I want to distinct count Emails (i.e. New Acqusitions)  wherein First_Enrolled date is MIN for the markets , brands user select from the slicer. so report have two slicers on i.e. Market and Brand. how to write a calculated measure (DAX) that can calculated on the fly distinct count of email for the MIN first_enrolled date based on the slicer selection user perform. first acquisition date change for the same person across markets and brands like here for steve if user select Pharma Market the first acquisition is 2/14/2022 but if we select Medicine market from the slicer then first acquisition date is 2/25/2023 so we can to consider date based on the market, brands user select from the slicers.

 

Any idea would be highly appreciated !!

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  ani_informa ,

    I add some data:

     

    Here are the steps you can follow：

    1. Create calculated table.

    Slicer =
SUMMARIZE(
    'Table','Table'[Market],'Table'[Brand])

    2. Create measure.

    Measure =
var _selectmarket=SELECTEDVALUE('Slicer'[Market])
var _selectbrand=SELECTEDVALUE('Slicer'[Brand])
var _mindate=
MINX(
    FILTER(ALL('Table'),
    'Table'[Market]=_selectmarket&&'Table'[Email]=MAX('Table'[Email])),[First_Enrolled])
return
IF(
    MAX('Table'[First_Enrolled]) = _mindate,
  COUNTX(
        FILTER(ALL('Table'),     'Table'[Email]=MAX('Table'[Email])&&'Table'[Market]=MAX('Table'[Market])&&'Table'[Brand]=_selectbrand&&'Table'[First_Enrolled]=_mindate),[Email]),BLANK())

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • ani_informa's avatar
      ani_informa
      Helper III
      3 years ago

      Thank You both for your responses !!

      I tried something like this and it is working fine and showing appropriate Acquisition counts across Brands, Markets if I plot stacked bar chart with Brands on one axis and measure Acqusition on another and same another bar chart for market. However when I am trying to plot a Trend line with Acquisition and Year, Month then person is getting counted in every month he has record in the fact table whereas as per Acqusition, person should get counted only one for the MIN date of his enrollment. 

       

      Acquisition =
      var _first_enrolled =
      ADDCOLUMNS
      (
           VALUES(fact_table[email])
          ,"MIN", CALCULATE(MIN(fact_table[first_enrolled]))
      )
      Return
      CALCULATE( COUNTROWS(_first_enrolled))
  • DOLEARY85's avatar
    DOLEARY85
    Resident Rockstar
    3 years ago

    Hi,

     

    try this:

     

    Measure =
    var _earliestdate = calculate(min('Data'[first enrolled]),ALLEXCEPT(Data,Data[brand],Data[email],Data[market]))

    return
    CALCULATE(DISTINCTCOUNT(Data[email]))
     
    -the bold part should bring back the date you require if you just want to see what it's doing,
     
    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

  • ani_informa's avatar
    ani_informa
    Helper III
    3 years ago

    This is still not working for me. Any other solution would be highly appreciated.

    My requirement is to count distinct emails considering Minimum of first_enrolled date for the Market or Brand user select from the slicer. so from the sample data, If user select "Pharma" Market from the slicer then [email protected] should be getting counted only once for the min of First_enrolled i.e. 2/14/2022 and [email protected] should be counted for 1/15/2022 (min date) so line chart should count steve under Feb 22 only and adam in Jan 22 only. Likewise if user select Brand then email for the min of first_enrolled should get counted. 

     

     

    • ani_informa's avatar
      ani_informa
      Helper III
      3 years ago

      Thanks for all the responses !!

       

      Able to get through this. I have created a measure like this. posting this just in case if anyone else end up receiving such requirement.

       

      First_Enrolled_date =
      CALCULATE (      
          MIN ( Fact_table[first_enrolled] ),
          REMOVEFILTERS ( date_dimension )  
      )

       

      Acqusition =
      var _first_engaged =
      ADDCOLUMNS
      (
           VALUES(Fact_table[email])
          ,"MIN", CALCULATE(MIN(Fact_table[First_Enrolled]))
      )
      Return
       COUNTROWS(FILTER(_first_engaged,[MIN]=[First_Enrolled_date]))