Dynamic accumulated grouping by running month

[Rita_Alix]

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:

Person	Registered Date
A	02-Jan-2020
B	15-Jan-2020
C	10-Mar-2020
D	12-Feb-2020
E	20-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-2020	Feb-2020	Mar-2020	Apr-2020	May-2020
NoOfPeople registered < 3 months	A, 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

[Rita_Alix]

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 · Accepted solution]

@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