Calculate cumulative distinct values whose amounts are not zero per date

[radiant]

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!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Jihwan_Kim]

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

[jdbuchanan71]

@radiant 

Try a measure along these lines.

Number_Of_Holders = CALCULATE ( DISTINCTCOUNT ( 'YourTable'[holder] ), 'YourTable'[amount] > 0 )

[radiant]

Thanks a lot! I am sorry, I asked it wrong. Corrected my question in initial  post.

[ryan_mayu]

@radiant 

not clear about how you get the result.  Did you only share a part of your sample data?

[radiant]

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.