cancel
Showing results 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

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:

 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.

Best Regards,

Alix

1 ACCEPTED SOLUTION
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

2 REPLIES 2
Frequent Visitor

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?
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