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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors