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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Rita_Alix
Frequent Visitor

Dynamic accumulated grouping by running month

Dear experts,

I need your help to visualise in matrix in PowerBI the accumulated number of people based on his/her registration date in each running calendar month.  Here is the example data.  I have 1 table that stores the person and when they registered:

PersonRegistered Date
A02-Jan-2020
B15-Jan-2020
C10-Mar-2020
D12-Feb-2020
E20-Apr-2020

Each month, I need to show where these people are grouped, i.e. 2 groups:

1.No of people registered < 3 months

2.No of people registered >= 3 months

Visualisation in matrix will be like this:

 Jan-2020Feb-2020Mar-2020Apr-2020May-2020
NoOfPeople registered < 3 monthsA, B (2)A, B, D (3)A, B, C, D (4)C, D, E (3)C, E (2)
NoOfPeople registered >= 3 months  A, B (2)A, B, D (3)

A, B, C, D, E in the matrix above are just to show where these people are each month.  I only need the numeric (no of people) in each running month.

 

Thank you in advance.

 

Best Regards,

Alix

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Rita_Alix , Create two measure like these with an independent date table and use month from that date table in visual

 

less than 3 =

calculate(count(Table[person]), filter(Table, datediff(Table[Registered Date], max('Date'[Date]),month)<3))

 

More than and equal to 3= calculate(count(Table[person]), filter(Table, datediff(Table[Registered Date], max('Date'[Date]),month)>=3))

 

 

refer if needed

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
Rita_Alix
Frequent Visitor

Hi @amitchandak 

Thank you for your swift reply.

The measures give the count when the person is registered, not accumulated.  I use the usual 

filter(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date] <= max('Calendar'[Date])) to get the accumulated count.
 
How to use these measures as rows instead of shown in the column?
amitchandak
Super User
Super User

@Rita_Alix , Create two measure like these with an independent date table and use month from that date table in visual

 

less than 3 =

calculate(count(Table[person]), filter(Table, datediff(Table[Registered Date], max('Date'[Date]),month)<3))

 

More than and equal to 3= calculate(count(Table[person]), filter(Table, datediff(Table[Registered Date], max('Date'[Date]),month)>=3))

 

 

refer if needed

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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