This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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])))
Hi @Sreeprasad KS , we have not get a reply from you. Could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!
Hi @Sreeprasad KS , we have not get a reply from you. Could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!
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" ) )
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.
Hi, made some update for the script:
Cummulative Employee1 = VAR DateMax = MAX ('Table'[Date])
Return
CALCULATE (
COUNTROWS(
FILTER (
ALL('Table'[Date],'Table'[Employe ID],'Table'[Refunded]),
'Table'[Date] <= DateMax && 'Table'[Refunded] = 0
)
)
)
