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 thou...
Need Help
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))