Matrix Cohort table - date filter issue

Question

Hello Community&nbsp; -&nbsp; I have followed this great tutorial (see link) and everything works as expected.&nbsp;&nbsp;&nbsp;The one "wish" I would like this measure to include is the ability to be controlled (accurately) by a date table.&nbsp; In the comments of the tutorial the author recognizes that filtering by the Year will yield incorrect results and he recommends to create a disconneted filter table.&nbsp; &nbsp;I'm trying to avoid that as I will have other visuals on the report page that work just fine with my date table&nbsp; (connected to sales orders, order date).&nbsp;&nbsp;&nbsp;Any gurus out there that could figure out how to modify the measure below to make this measurable filterable by the date table and still keep the correct results in the matrix?&nbsp;&nbsp;&nbsp;Power BI: Cohort Analysis Full Tutorial - Finance BI (finance-bi.com)New Customers Count = VAR acquisitionMonth =&nbsp; &nbsp; SELECTEDVALUE ( 'Dim_Order Date Table'[Year Month order] )VAR selectedFutureMonth =&nbsp; &nbsp; SELECTEDVALUE ( 'Future months'[Value] )VAR currentCustomers =&nbsp; &nbsp; VALUES ( 'SalesOrdersALL'[Bill To Customer_CustNum] )VAR pastCustomers =&nbsp; &nbsp; CALCULATETABLE (&nbsp; &nbsp; &nbsp; &nbsp; VALUES ( SalesOrdersALL[Bill To Customer_CustNum] ),&nbsp; &nbsp; &nbsp; &nbsp; ALL ( 'Dim_Order Date Table'[Month &amp; Year], 'Dim_Order Date Table'[MonthnYear] ),&nbsp; &nbsp; &nbsp; &nbsp; 'Dim_Order Date Table'[Year Month order] &lt; acquisitionMonth&nbsp; &nbsp; )VAR newCustomers =&nbsp; &nbsp; EXCEPT ( currentCustomers, pastCustomers )VAR customersFuturePeriods =&nbsp; &nbsp; CALCULATETABLE (&nbsp; &nbsp; &nbsp; &nbsp; VALUES ( SalesOrdersALL[Bill To Customer_CustNum] ),&nbsp; &nbsp; &nbsp; &nbsp; ALL ( 'Dim_Order Date Table'[Month &amp; Year], 'Dim_Order Date Table'[MonthnYear] ),&nbsp; &nbsp; &nbsp; &nbsp; 'Dim_Order Date Table'[Year Month order] = acquisitionMonth + selectedFutureMonth&nbsp; &nbsp; )VAR newCustomersBuyingInFuturePeriods =&nbsp; &nbsp; INTERSECT ( newCustomers, customersFuturePeriods )RETURN&nbsp; &nbsp; &nbsp;COUNTROWS ( newCustomersBuyingInFuturePeriods )

v-luwang-msft · Answer

Hi&nbsp;Anonymous&nbsp;，
Can you post sample data as text and expected output?Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882&nbsp;
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490&nbsp;
&nbsp;
&nbsp;
Best Regards
Lucien

Forum Discussion

Matrix Cohort table - date filter issue

1 Reply

Related content

Cohort analysis

A cohort analysis (matrix visual)

Unpivoted Survey Responses Cohort Matching - Visual Filters based on response

Field Parameters + multiple calendar systems: how to filter a cohort matrix by a single selected yea

Matrix table