Calculation before aggregation

[0tkali]

I wanted to calculate new customers added every month based on the data below

I want below output

Calculation I tried

if(min(total [Order],[Customer],[Month.autoCalendar.YearMonth] [Month])=min(total <[Customer]>[Month]),1,0)

I get 0 for all the rows when I have only month in a straight table along with the calculation. I am able to see correct calculated values per customer when I add customer dimension to the table but once I remove the customer dimension is the values are all wrong again. Can someone please help.

[Shubham_Deshmukh]

Use this :

Dimension  : =Monthname(Month)

Expression : =count(customer)

[0tkali]

this will give count of customers per month. What I want is count of new customers per month. If a customer is repeated in subsequent months, should not be counted.

[Shubham_Deshmukh]

Got it, try this : 

=Count(DISTINCT {<customer = {"=Count({<Month= {'<=$(=Date(Max(Month)))'}>}customer) =1"}>} customer)

Note : Pls change column name of dates into something else, don't keep it Month.

[0tkali]

I used this

=Count(DISTINCT {<[Customer Name] = {"=Count({<Month= {'<=$(=Date(Max([Order Date])))'}>} [Customer Name] ) =1"}>} [Customer Name]) 

Count for Jan 2015 should be 32 but  I get 0.

[Shubham_Deshmukh]

You are comparing month to date 

=Count(DISTINCT {<[Customer Name] = {"=Count({<Month= {'<=$(=Date(Max([Order Date])))'}>} [Customer Name] ) =1"}>} [Customer Name]) 

If you have order date separate column then you can use : 

=Count(DISTINCT {<[Customer Name] = {"=Count({<[Order Date]= {'<=$(=Date(Max([Order Date])))'}>} [Customer Name] ) =1"}>} [Customer Name]) 

[0tkali]

Yes I did try this as well. All values are 0

[0tkali]

Can you please explain what the expression does within the set {}

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(If(Customer = Above(Customer), 0, 1), Customer, (Month, (NUMERIC))))

[0tkali]

This sort of almost works, can you please explain how If(Customer = Above(Customer), 0, 1) works. Does it get executed for every row before aggregation?