DAX: Create a column where I count frequency based on date range from another table

[Joana_]

I am really struggling with this one - hoping some one can help me.

I want to count for each customer the number of months. I have two key tables:

Tabel_10 :

- Customer

- Customer_Group

- Date

- Date_L12

Tabel_9

- Customer

- Customer_Group

- Date

The two table are joined by the key "Customer".

So what I want, is that:

for each customer in table_10 I want to see how many times he appears in table_9 between the data range tabel_10[Date] & Table_10[Date_L12]

So what I want is a new column "Frequency" in Tabel_10. 

[Anonymous · Accepted solution]

Hi  @Joana_ ,

I created some data:

Table_9:

Table_10:

Here are the steps you can follow：

1. Create calculated column.

Flag =
var _table10=
SELECTCOLUMNS('Table_10',"1",[Customer])
var _if=
IF(
    'Table_9'[Customer] in _table10
    &&
 'Table_9'[Date]>=MAXX(FILTER(ALL(Table_10),'Table_10'[Customer]=EARLIER('Table_9'[Customer])),[Date])
    &&
    'Table_9'[Date]<=MAXX(FILTER(ALL(Table_10),'Table_10'[Customer]=EARLIER('Table_9'[Customer])),[Date_L12])
    ,1,0)
return
_if

Column =
COUNTX(
    FILTER(ALL(Table_9),
    'Table_9'[Flag]=1),[Customer])

 2. 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

[Joana_]

P.S. the column about the "Date" are based on months.