# Cumulative SUM of Distinct count in DAX 1 Reputation point
2021-11-25T11:41:11.997+00:00

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])))
``````
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,006 questions

1. 2,361 Reputation points
2021-11-25T21:36:36.83+00:00

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" ) )

2. 6,796 Reputation points Microsoft Employee
2021-11-26T09:38:03.903+00:00

Welcome to Microsoft Q&A!

``````Cummulative Employee = VAR DateMax = MAX ('Table'[Date])

Return

CALCULATE (

COUNTROWS(

FILTER (

ALL('Table'[Date],'Table'[Employe ID]),

'Table'[Date] <= DateMax

)

)

)
`````` Best regards,
Carrin