DAX Optimisation Cumulative DISTINCTCOUNT
Hi All, I am wondering if someone can help me with a very slow DAX calculation. Business Case: We consider a customer to be financially active on our system for a financial year if the sum of their transactions for a financial year for any Group ID is greater than 0. Our financial year lasts from 1st of August and ends 31st of July. Product ID's are grouped under a Grouping ID. DAX I created a grouping calculated column in the customer table, (Fiscal Year || Grouping ID || Customer ID) { to avoid having to create joins in the query step). I want to calculate the number of financially active customers ( Active Customer Count ) and a cumulative count of this metric (Cumulative Active Customer Count). However the cumulative sum is very slow (20-30 seconds long) Active Customer Count = CALCULATE( DISTINCTCOUNT('Customer Tbl'[Customer ID]), FILTER( ALL('Customer Tbl'[Fiscal Year || Grouping ID || Customer ID]), [Transaction Amnt]>0)) Cumulative Active Customer Count = CALCULATE( [Active Customer Count], CALCULATETABLE( DATESYTD('Dim Date'[Date], "31-07"), 'Dim Date'[Is Future Date] = "Not Future Date" ) ) Any idea on what I can do to speed up this cumulative count? Link to file: https://drive.google.com/file/d/18OuoPSx2pFmk0Q-NCU1hhPcFyt_pQvr3/view?usp=sharing
