Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gracechilton
Regular Visitor

Count number of accounts with same end date

Hi,

 

I'm trying to find for each date in a specific range, how many accounts had their most recent billing period end on that date. I'm able to find, by account, the most recent billing period end date, and I can also see, by date, how many accounts have A billing period that ends on that date. However, I'm unable to view date by date the number of accounts that have their LATEST billing period end on that date.

 

If anyone has any ideas on how to help with this, that would be great.

2 ACCEPTED SOLUTIONS

Whoops, left out an ALL:

 

Measure 2 = 
VAR __BillingDate = MAX('Billing'[Billing Period End Date])
VAR __tmpTable = SUMMARIZE(ALL(Billing),Billing[Account ID],"__lastBillingDate",MAX([Billing Period End Date]))
RETURN
COUNTROWS(FILTER(__tmpTable,[__lastBillingDate]=__BillingDate))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@gracechilton,

 

Simply use ALLSELECTED Function instead of ALL Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and show the expected result.


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

Sounds like you want to use a measure based on COUNTROWS. Need sample data and expected output to be sure though. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Sample data:

 

Billing Period End DateAccount ID
3/1/201865408709
3/1/201861976330
3/1/201861518263
3/1/201863378202
3/1/20182059023
3/2/201865598703
3/3/201869854130
3/4/201898706394
3/5/201848253255
3/6/201818760985
3/6/201856723352
3/6/201850031249
3/7/201876424211
3/8/201873139101
3/9/20188346090
3/10/20185325690
3/11/201817540588
3/11/201829788880
3/11/201871080128
3/11/201896637607
3/11/201880160021
3/12/201833715633
3/13/201821855366
3/14/201880367090
3/15/201882159931
3/16/201823031524
3/17/201840554298
3/18/201832347047
3/19/20185544891
3/20/201867757320
3/21/201894941078
3/22/201847641317
3/23/201842202471
3/23/201883125040
3/23/201819423294
3/23/201863350761
3/23/201852605386
3/23/201831842991
3/23/20184038012
3/24/201884978006
3/25/201826902050
3/26/201820645214
3/27/201836224624
3/27/201827717983
3/27/201827471557
3/27/201898424957
3/27/201892699247
3/28/201827049511
3/29/201895953684
3/30/201855415155
3/31/201884088080
4/1/201865408709
4/2/201863378202
4/3/20182059023
4/4/201865598703
4/5/201869854130
4/6/201898706394
4/6/201829857571
4/6/201827197347
4/6/201836740309
4/6/201876460974
4/6/201872855054
4/6/201843617869
4/7/201850031249
4/8/201817540588
4/9/201829788880
4/10/201871080128
4/11/201896637607
4/12/201880160021
4/13/201833715633
4/14/201823031524
4/15/20185544891
4/16/201883125040
4/17/201873139101
4/18/201817540588
4/19/201880160021
4/20/201821855366
4/21/201882159931
4/22/201867757320
4/23/201842202471
4/24/201863350761
4/25/201831842991
4/26/20184038012
4/27/201826902050
4/28/201836224624
4/29/201829857571
4/30/201827197347

 

 

Expected output:

 

These counts aren't correct with respect to the sample data, but hopefully this gives the idea of what I'm looking for. In the # of accounts, I want to see the number of accounts that have their most recent billing period end on that date, not just the number of accounts that have a billing period end on that date. So, for example, even though an account may have had 2 billing periods ending on the dates 3/5/2018 and 4/12/2018, I only want to see that account counted on 4/12/2018.

 

 

Billing Period End Date# of accounts
3/1/20183
3/2/20182
3/3/20181
3/4/20182
3/5/20182
3/6/20182
3/7/20181
3/8/20182
3/9/20183
3/10/20183
3/11/20182
3/12/20182
3/13/20183
3/14/20181
3/15/20182
3/16/20182
3/17/20182
3/18/20181
3/19/20183
3/20/20182
3/21/20181
3/22/20183
3/23/20183
3/24/20182
3/25/20183
3/26/20182
3/27/20181
3/28/20183
3/29/20183
3/30/20183
3/31/20181
4/1/20181
4/2/20181
4/3/20183
4/4/20183
4/5/20182
4/6/20181
4/7/20182
4/8/20182
4/9/20183
4/10/20182
4/11/20183
4/12/20181
4/13/20181
4/14/20182
4/15/20182
4/16/20181
4/17/20182
4/18/20181
4/19/20182
4/20/20182
4/21/20183
4/22/20182
4/23/20182
4/24/20183
4/25/20181
4/26/20183
4/27/20183
4/28/20183
4/29/20181
4/30/20182

Hi,

 

You may refer to my solution in this file.

 

Hope this helps.

 

Accounts last billed on.png


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

How do I filter for the correct date range? I want the period March 1 - May 31 2018, but my data goes back for years and years until present.

@gracechilton,

 

Simply use ALLSELECTED Function instead of ALL Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

See if this works:

 

Measure  = 
VAR __BillingDate = MAX('Table'[Billing Period End Date])
VAR __tmpTable = SUMMARIZE('Table','Table'[Account ID],"__lastBillingDate",MAX([Billing Period End Date]))
RETURN
COUNTROWS(FILTER(__tmpTable,[__lastBillingDate]=__BillingDate))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Gave me the same result as Count (distinct).

 

I should also mention I am already filtering the date range.

Whoops, left out an ALL:

 

Measure 2 = 
VAR __BillingDate = MAX('Billing'[Billing Period End Date])
VAR __tmpTable = SUMMARIZE(ALL(Billing),Billing[Account ID],"__lastBillingDate",MAX([Billing Period End Date]))
RETURN
COUNTROWS(FILTER(__tmpTable,[__lastBillingDate]=__BillingDate))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hmm. Still not working for me. I think probably because my data source isn't actual tables, it's a data anaylyzer. Thanks for your help though, definitely closer than I would have gotten.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.