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

Thanks:  0
Likes:  0

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

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

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:
````[New Customers] := `
`COUNTROWS` `(`
`    ``FILTER` `(`
`        ``ADDCOLUMNS` `(`
`            ``VALUES` `(` `Sales[CustomerKey] ``)``,`
`            ``"PreviousSales"``, ``CALCULATE` `(`
`                ``COUNTROWS` `(` `Sales ``)``,`
`                ``FILTER` `(`
`                    ``ALL` `(` `'Date'` `)``,`
`                    ``'Date'``[FullDate] < ``MIN` `(` `'Date'``[FullDate] ``)`
`                ``)`
`            ``)`
`        ``)``,`
`        ``[PreviousSales] = 0`
`    ``)`
`)````

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

2. ## Re: New Customer (under certain conditions) - How to ? DAX

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•