cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
radiant
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.

dateholderoperationsum
01/01/2021Mike1010
01/02/2021Sam2020
02/03/2021Mike919
02/03/2021Sam121
05/04/2021Mike-190
06/04/2021Sam223
07/05/2021Tom33

 

Result should be

datecount
01/01/20211
01/02/20212
02/03/20212
05/04/20211
06/04/20211
07/05/20212

how can we build a report/measure to get Distinct count of Holders/day who have amount > 0?

 

Thanks in advance!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Jihwan_Kim
Community Champion
Community Champion

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.

 

 

Picture1.png

 

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 accept it as the solution to help other members find it faster, and give a big thumbs up.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

jdbuchanan71
Super User II
Super User II

@radiant 

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.

@radiant 

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





Did I answer your question? Mark my post as a solution!

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors