Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
We have a table with columns for a period of few years:
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!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, @radiant
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Try a measure along these lines.
Number_Of_Holders = CALCULATE ( DISTINCTCOUNT ( 'YourTable'[holder] ), 'YourTable'[amount] > 0 )
Thanks a lot! I am sorry, I asked it wrong. Corrected my question in initial post.
not clear about how you get the result. Did you only share a part of your sample data?
Proud to be a Super User!
that is the whole sample data.
The logic is the following:
- for each date, calculate the Count of Unique holders whose balance is positive. That would give us a nice chart of Holders over time;
- the holder balance is not stored directly, but can be calculated by walking through transactions (In/Out). This is something we cannot change. So to calculate a balance of holder ad date X, we should summ up all the transactions <= that date.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |