Active Customers - countif used in Power BI

[PawelMJ]

Hi,

I'm transfering manual Excel report to Power BI and I have one issue for the time being.

Database has 3 tables: Sales, Data(calendar), Customers.

Active Customer, is a customer, which had purchased product:

1. at least in 3 out of 12 last months and 

2. at least in 1 out of 3 last month

I though about adding in Customer table column, which will say if customer is Active or not.

Thank you for your support !

Example table in Excel below:

Customer	1901	1902	1903	1904	1905	1906	1907	1908	1909	1910	1911	1912		Purchase in last 12 months >= 3	Purchase in last 3 months >= 1	Active
A	$          10	$          10	$          10	$          10	$          10	$          10	$           -	$          10	$           -	$           -	$          10	$          10		9	2	YES
B	$           -	$           -	$           -	$           -	$           -	$           -	$          10	$           -	$          10	$           -	$           -	$           -		2	0	NO
C	$          10	$           -	$           -	$           -	$           -	$          10	$           -	$           -	$           -	$           -	$           -	$          10		3	1	YES

[Anonymous]

Hi @PawelMJ ,

If you already did that column that can help a lot in the measure complexity.

Having that column you only have to use Calculate(count('Customers'[ID]),[Active]="YES")

Let me know if it helped, if so mark as solution.

Best Regards,

Duarte Raminhos

[PawelMJ]

Thank you for your replay.

The problem is, that I can't find any simple solution to make logic behind this colum. So I haven't created it. This is the issue.

BR,

Pawel

[Anonymous]

Oh alright I dind't get it.

To simplify things you can create both columns like:

3 months = if(now()-90<=[Date],1,0) to assign a flag to every entry.

1 Month =  if(now()-30<=[Date],1,0) to assign a flag to every entry.

Then last if:

Active/Inactive = if(and([3 months]=1,[1 Month]=1,"Active","Inactive")

To count the active orders use a measure that goes like:

measure = calculate(count([orderID]),Active/Inactive="Active")

With this logic you're categorizing every order and when you do the visual every customer that counts 0 is inactive and it will only consider the orders you really want.

Let me know if it helped.

Best Regards,

Duarte Raminhos

[PawelMJ]

Simple, but unfortunately, won't work.

With this approach, we have information for today.

If we want to see active customers in last month or 6 month ago, it won't work.

[Anonymous]

Could you provide a sample of your data model so I can understand what paths you have available?

Best Regards,

Duarte Raminhos

[PawelMJ]

I've created sample of data model. Hope that this will help you to understand the problem.

DataModelSample