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

The 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.

Reply
kevinedora
Helper I
Helper I

Max and Count

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:

 

AccountNumberDateAge Bucket
A01/01/20230
B01/01/202330
C01/01/202360
D01/01/202390
E01/01/20230
A02/01/20230
A02/01/202330
B02/01/20230
C02/01/202390
D02/01/202330
E02/01/202330

 

And this is the result I'm trying to get (visual on power bi, A table, or matrix)

 

 Current Month     
 Buckets030 Days60 Days90 DaysTotal
Previous Month

0

 2  2
 30 Days1   1
 60 Days   11
 90 Days 1  1
 Total13 15

 

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!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Here is a Power Query option based on your sample data. Please provide sample data that fully covers your scenario.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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