Cumulative SUM of Distinct count in DAX

Sreeprasad KS 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

152631-image.png

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
{count} votes

2 answers

Sort by: Most helpful
  1. Darren Gosbell 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" ) )

    No comments

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

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

    152911-dax.png

    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.