Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I've been trying to work this recently but I can't find a way to do it. Please help.
I have this kind of database:
AccountNumber | Date | Age Bucket |
A | 01/01/2023 | 0 |
B | 01/01/2023 | 30 |
C | 01/01/2023 | 60 |
D | 01/01/2023 | 90 |
E | 01/01/2023 | 0 |
A | 02/01/2023 | 0 |
A | 02/01/2023 | 30 |
B | 02/01/2023 | 0 |
C | 02/01/2023 | 90 |
D | 02/01/2023 | 30 |
E | 02/01/2023 | 30 |
And this is the result I'm trying to get (visual on power bi, A table, or matrix)
Current Month | ||||||
Buckets | 0 | 30 Days | 60 Days | 90 Days | Total | |
Previous Month | 0 | 2 | 2 | |||
30 Days | 1 | 1 | ||||
60 Days | 1 | 1 | ||||
90 Days | 1 | 1 | ||||
Total | 1 | 3 | 1 | 5 |
I need to count the account number based on their age bucket per current month (e.g. Feb 2023) and previous month (e.g. Jan 2023). (e.g. Account number "B", one count on the "current" month column "0 days" and in the "previous" month row, it should be placed on the "30 days".
Also, just an added rule if the account number has multiple entries for one month, the count should be only 1 and it should capture the highest or max-age bucket. (e.g. Account number "A" on Feb, there are 2 entries the count should be for the Age bucket "30 days" for the current month, and for the previous month, it should be placed on 0 days).
Note: this is also historical so I might use a filter to change the month/date for checking older months' results.
Please advise. Thank you!
Solved! Go to Solution.
Here is a Power Query option based on your sample data. Please provide sample data that fully covers your scenario.
This already works on my end. Thank you!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |