Cumulative Distinct Count with variables

[Kevin_Conseil]

Hi Everyone,

Background: 

• I have a dataset recording visits of sales managers in stores
• Each visit create multiple entry lines as the sales managers try to sell multiple products to the store
• Each store has a performance categorie (from A - best - to C - worse) and its categorie can change thoughout the time
• A store can be visited once a month at most but not all stores are visited in a month
• A store is visited by one single sales manager

See screenshot below of the sample dataset i have (only for one store and one sales manager)

What I want to achieve:   

I d like to show the development (per month) of the performance of the stores per sales managers per store categorie on a deep dive level (per store) and aggregated level. Therefore I d like to do the cumulative distinct count of stores, taking into account the store categorie, sales managers and month

See screenshots below of the tables I d like to get:

What I have:   

I have created a measure that calculated the cumulative distinct count of stores and get filters per store catgeorie, sales manager & month (see DAX code below). However as soon as a store change categorie, it continues counting the store for the old categorie and start counting for the new categorie 

# cumulative stores = VAR results = CALCULATE( DISTINCTCOUNT('Reports'[store id]), FILTER(ALLSELECTED(Date),date[Date]<= MAX(date[Date]))) RETURN results

See screenshot below of the table i get using the above measure:

Please let me know if you have a solution :), 

Best, 

Kevin 

[V-pazhen-msft · Accepted solution]

@Kevin_Conseil 
If you can create a short sample and put it into pbix, it would be clear for me. For now, you can try add additional filters in the measure, because you are looking to count and filter by the salesman and categories.

result = CALCULATE( DISTINCTCOUNT('Reports'[store id]), FILTER(ALLSELECTED(Date),[Salesman]=max([Salesman]) && [categories]=max([categories]) && date[Date]<= MAX(date[Date])))

 
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.