Forum Discussion
Calculate cumulative distinct values whose amounts are not zero per date
We have a table with columns for a period of few years:
- holder
- date
- amount (this is an income or deductions for this date for holder)
Data looks like, where sum - is a measure.
| date | holder | operation | sum |
| 01/01/2021 | Mike | 10 | 10 |
| 01/02/2021 | Sam | 20 | 20 |
| 02/03/2021 | Mike | 9 | 19 |
| 02/03/2021 | Sam | 1 | 21 |
| 05/04/2021 | Mike | -19 | 0 |
| 06/04/2021 | Sam | 2 | 23 |
| 07/05/2021 | Tom | 3 | 3 |
Result should be
| date | count |
| 01/01/2021 | 1 |
| 01/02/2021 | 2 |
| 02/03/2021 | 2 |
| 05/04/2021 | 1 |
| 06/04/2021 | 1 |
| 07/05/2021 | 2 |
how can we build a report/measure to get Distinct count of Holders/day who have amount > 0?
Thanks in advance!
6 Replies
- jdbuchanan71
Super User
Anonymous
Try a measure along these lines.
Number_Of_Holders = CALCULATE ( DISTINCTCOUNT ( 'YourTable'[holder] ), 'YourTable'[amount] > 0 )
ryan_mayuAnonymous
not clear about how you get the result. Did you only share a part of your sample data?
- Jihwan_Kim
Hi, Anonymous
Please check the below picture and the measure, whether it is what you are looking for.
The sample pbix file's link is down below.
Counting holders =
VAR currentdate =
MAX ( 'Table'[date] )
VAR untilcurrentdatetable =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[date] <= currentdate ),
'Table'[holder],
"@operationsum", SUM ( 'Table'[operation] )
)
VAR filtergreaterthanzero =
FILTER ( untilcurrentdatetable, [@operationsum] > 0 )
RETURN
COUNTROWS ( filtergreaterthanzero )https://www.dropbox.com/s/djmhus2tn1cdcr9/radiant.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
- Ashish_Mathur
