Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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))
Simply use ALLSELECTED Function instead of ALL Function.
Hi,
Share some data and show the expected result.
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
Sample data:
Billing Period End Date | Account ID |
3/1/2018 | 65408709 |
3/1/2018 | 61976330 |
3/1/2018 | 61518263 |
3/1/2018 | 63378202 |
3/1/2018 | 2059023 |
3/2/2018 | 65598703 |
3/3/2018 | 69854130 |
3/4/2018 | 98706394 |
3/5/2018 | 48253255 |
3/6/2018 | 18760985 |
3/6/2018 | 56723352 |
3/6/2018 | 50031249 |
3/7/2018 | 76424211 |
3/8/2018 | 73139101 |
3/9/2018 | 8346090 |
3/10/2018 | 5325690 |
3/11/2018 | 17540588 |
3/11/2018 | 29788880 |
3/11/2018 | 71080128 |
3/11/2018 | 96637607 |
3/11/2018 | 80160021 |
3/12/2018 | 33715633 |
3/13/2018 | 21855366 |
3/14/2018 | 80367090 |
3/15/2018 | 82159931 |
3/16/2018 | 23031524 |
3/17/2018 | 40554298 |
3/18/2018 | 32347047 |
3/19/2018 | 5544891 |
3/20/2018 | 67757320 |
3/21/2018 | 94941078 |
3/22/2018 | 47641317 |
3/23/2018 | 42202471 |
3/23/2018 | 83125040 |
3/23/2018 | 19423294 |
3/23/2018 | 63350761 |
3/23/2018 | 52605386 |
3/23/2018 | 31842991 |
3/23/2018 | 4038012 |
3/24/2018 | 84978006 |
3/25/2018 | 26902050 |
3/26/2018 | 20645214 |
3/27/2018 | 36224624 |
3/27/2018 | 27717983 |
3/27/2018 | 27471557 |
3/27/2018 | 98424957 |
3/27/2018 | 92699247 |
3/28/2018 | 27049511 |
3/29/2018 | 95953684 |
3/30/2018 | 55415155 |
3/31/2018 | 84088080 |
4/1/2018 | 65408709 |
4/2/2018 | 63378202 |
4/3/2018 | 2059023 |
4/4/2018 | 65598703 |
4/5/2018 | 69854130 |
4/6/2018 | 98706394 |
4/6/2018 | 29857571 |
4/6/2018 | 27197347 |
4/6/2018 | 36740309 |
4/6/2018 | 76460974 |
4/6/2018 | 72855054 |
4/6/2018 | 43617869 |
4/7/2018 | 50031249 |
4/8/2018 | 17540588 |
4/9/2018 | 29788880 |
4/10/2018 | 71080128 |
4/11/2018 | 96637607 |
4/12/2018 | 80160021 |
4/13/2018 | 33715633 |
4/14/2018 | 23031524 |
4/15/2018 | 5544891 |
4/16/2018 | 83125040 |
4/17/2018 | 73139101 |
4/18/2018 | 17540588 |
4/19/2018 | 80160021 |
4/20/2018 | 21855366 |
4/21/2018 | 82159931 |
4/22/2018 | 67757320 |
4/23/2018 | 42202471 |
4/24/2018 | 63350761 |
4/25/2018 | 31842991 |
4/26/2018 | 4038012 |
4/27/2018 | 26902050 |
4/28/2018 | 36224624 |
4/29/2018 | 29857571 |
4/30/2018 | 27197347 |
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/2018 | 3 |
3/2/2018 | 2 |
3/3/2018 | 1 |
3/4/2018 | 2 |
3/5/2018 | 2 |
3/6/2018 | 2 |
3/7/2018 | 1 |
3/8/2018 | 2 |
3/9/2018 | 3 |
3/10/2018 | 3 |
3/11/2018 | 2 |
3/12/2018 | 2 |
3/13/2018 | 3 |
3/14/2018 | 1 |
3/15/2018 | 2 |
3/16/2018 | 2 |
3/17/2018 | 2 |
3/18/2018 | 1 |
3/19/2018 | 3 |
3/20/2018 | 2 |
3/21/2018 | 1 |
3/22/2018 | 3 |
3/23/2018 | 3 |
3/24/2018 | 2 |
3/25/2018 | 3 |
3/26/2018 | 2 |
3/27/2018 | 1 |
3/28/2018 | 3 |
3/29/2018 | 3 |
3/30/2018 | 3 |
3/31/2018 | 1 |
4/1/2018 | 1 |
4/2/2018 | 1 |
4/3/2018 | 3 |
4/4/2018 | 3 |
4/5/2018 | 2 |
4/6/2018 | 1 |
4/7/2018 | 2 |
4/8/2018 | 2 |
4/9/2018 | 3 |
4/10/2018 | 2 |
4/11/2018 | 3 |
4/12/2018 | 1 |
4/13/2018 | 1 |
4/14/2018 | 2 |
4/15/2018 | 2 |
4/16/2018 | 1 |
4/17/2018 | 2 |
4/18/2018 | 1 |
4/19/2018 | 2 |
4/20/2018 | 2 |
4/21/2018 | 3 |
4/22/2018 | 2 |
4/23/2018 | 2 |
4/24/2018 | 3 |
4/25/2018 | 1 |
4/26/2018 | 3 |
4/27/2018 | 3 |
4/28/2018 | 3 |
4/29/2018 | 1 |
4/30/2018 | 2 |
Hi,
You may refer to my solution in this file.
Hope this helps.
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.
Simply use ALLSELECTED Function instead of ALL Function.
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))
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))
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |