Forum Discussion
DAX: Create a column where I count frequency based on date range from another table
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.
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 _ifColumn = 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
2 Replies
P.S. the column about the "Date" are based on months.
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 _ifColumn = 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
