Calculate First Acquisition

[ani_informa]

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

[ani_informa]

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 steve@gmail.com should be getting counted only once for the min of First_enrolled i.e. 2/14/2022 and adam@hotmail.com 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]

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

[v-yangliu-msft]

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]

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]

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 👍