Cumulative SUM of Distinct count in DAX

Sreeprasad KS 1 Reputation point

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

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

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

Cummulative Employee :=  
VAR ABCDE = DATEDIFF(MIN('Table'[Date]), MAX('Table'[Date]), DAY )  
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,231 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Darren Gosbell 2,371 Reputation points

    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 )
    SUMX( DATESBETWEEN(Time[Day],LASTDATE('Table'[Date])-

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

    1 person found this answer helpful.
    0 comments No comments

  2. CarrinWu-MSFT 6,841 Reputation points

    Hi @Sreeprasad KS ,

    Welcome to Microsoft Q&A!

    Please try below script:

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


    Best regards,

    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.