cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors