Cumulative SUM of Distinct count in DAX

Question

I'm trying to find the cumulative sum of distinct employee id for the specific days. I'm able to find the distinct employee id for the days with the below query, but the cumulative sum is not working. could anyone help me on this?

Sample data

Query to find the distinct employee Id -- This gives me the expected output

  DISTINCTCOUNTEMPLOYEE(Helper):=  
  CALCULATE(SUMX(SUMMARIZE('Table','Table'[Employe ID],   
  "Count",CALCULATE(DISTINCTCOUNT('Table'[Employe ID]),FILTER('Table','Table'[Refunded]="0"))),   
  [Count]))  

Query to find the cumulative sum -- Doesn't give me expected output

Cummulative Employee :=  
VAR ABCDE = DATEDIFF(MIN('Table'[Date]), MAX('Table'[Date]), DAY )  
RETURN  
CALCULATE([DISTINCTCOUNTEMPLOYEE(Helper)],DATESBETWEEN(Time[Day],LASTDATE('Table'[Date])-   
ABCDE,LASTDATE('Table'[Date])))

Answer 1

There is actually nothing in your second expression that is doing a cumulative sum, you are just evaluating the distinct count measure over a range of dates. If you did a SUMX over the dates I think you should get the output you are after.

Cummulative Employee :=
VAR ABCDE = DATEDIFF(MIN('Table'[Date]), MAX('Table'[Date]), DAY )
RETURN
SUMX( DATESBETWEEN(Time[Day],LASTDATE('Table'[Date])-
ABCDE,LASTDATE('Table'[Date])) , [DISTINCTCOUNTEMPLOYEE(Helper)] )

I also can't see what value the SUMX and SUMMARIZE calls are adding in your other measure. Have you tried a simpler expression like the following? It should return the same result, but with much less overhead.

DISTINCTCOUNTEMPLOYEE(Helper):= CALCULATE( DISTINCTCOUNT( 'Table'[Employe ID] ),FILTER( values('Table'[Refunded]), 'Table'[Refunded]="0" ) )

Answer 2

Hi @Sreeprasad KS ,

Welcome to Microsoft Q&A!

Please try below script:

Cummulative Employee = VAR DateMax = MAX ('Table'[Date])  
  
Return  
  
CALCULATE (  
  
COUNTROWS(  
  
FILTER (  
  
ALL('Table'[Date],'Table'[Employe ID]),  
  
'Table'[Date] <= DateMax  
  
)  
  
)  
  
)  

Best regards,
Carrin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.