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!
