cancel
Showing results for
Did you mean:
Frequent Visitor

## 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?

1 ACCEPTED SOLUTION
Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

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 )

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.

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.

Best regards, JiHwan Kim

Super User II

Try a measure along these lines.

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

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

Super User II

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

Proud to be a Super User!

Frequent Visitor

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.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks