New Customer (under certain conditions) - How to ? DAX

[Arnaud81]

Hi Everyone,

It have been struggling with this for 2 days..

Case :

1 Date table (with Full date, Year, Month)
1 Product table (with Product = Product A, B, C, D, E....)
1 Sales Table (with CustomerKey, Unit,...)

X products (A, B, C, D,....).

Pivot Table : Rows = CustomerKey ; Columns = Months

1) I need to calculate the new customers for Product A. It can be done with the measure below (from SQLBI) when filtering the Pivot with Product A.

However, I don't want to limit the scope to Product A cause I have got another condition to take into account.

New Customer = at least 1 unit of Product A, and 0 unit of (Product B or Product C) since begining of the year.

How can I adapt the measure here below to achieve what I want ?

I tried to add another column to the ADDCOLUMN function (in the measure below) : YTD units of Product B and Product C and then filtering this new column to zero, but it does not help...

<code class="dax plain">[New Customers] := </code>
<code class="dax color2">COUNTROWS</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">    </code><code class="dax color2">FILTER</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">        </code><code class="dax color2">ADDCOLUMNS</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">            </code><code class="dax color2">VALUES</code> <code class="dax parenthesis">(</code> <code class="dax plain">Sales[CustomerKey] </code><code class="dax parenthesis">)</code><code class="dax plain">,</code>
<code class="dax spaces">            </code><code class="dax string">"PreviousSales"</code><code class="dax plain">, </code><code class="dax color2">CALCULATE</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">                </code><code class="dax color2">COUNTROWS</code> <code class="dax parenthesis">(</code> <code class="dax plain">Sales </code><code class="dax parenthesis">)</code><code class="dax plain">,</code>
<code class="dax spaces">                </code><code class="dax color2">FILTER</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">                    </code><code class="dax color2">ALL</code> <code class="dax parenthesis">(</code> <code class="dax string">'Date'</code> <code class="dax parenthesis">)</code><code class="dax plain">,</code>
<code class="dax spaces">                    </code><code class="dax string">'Date'</code><code class="dax plain">[FullDate] < </code><code class="dax color2">MIN</code> <code class="dax parenthesis">(</code> <code class="dax string">'Date'</code><code class="dax plain">[FullDate] </code><code class="dax parenthesis">)</code>
<code class="dax spaces">                </code><code class="dax parenthesis">)</code>
<code class="dax spaces">            </code><code class="dax parenthesis">)</code>
<code class="dax spaces">        </code><code class="dax parenthesis">)</code><code class="dax plain">,</code>
<code class="dax spaces">        </code><code class="dax plain">[PreviousSales] = 0</code>
<code class="dax spaces">    </code><code class="dax parenthesis">)</code>
<code class="dax parenthesis">)</code>

2) And then I would like to calculate how many units of product A these new customers purchased since begining of the year and not only for the month they became a new Customer

Thanks a lot for your help

Arnaud

 

[Arnaud81]

That's fine, I found out the solution.

I added another column in the ADDCOLUMNS function that calculates if there were sales for Product B and C before or during the month in the row context of the pivot table and then filtered this new column to 0 with the Filter function surrounding the ADDCOLUMNS function.

I also added a VALUES(Date(Year)) in both calculate functions in order to keep active my Year filter in my Pivot Table so what happened in 2016 is not taken into account for 2017.

Cheers