Number of active rows by max stage reached at start of month

[Anonymous]

I have a good challenge here.

My data looks like

PersonStatus:

I have relation from Date to a Dates table (this where Month comes from).
I also have a relation between PersonStatus and a Status table (StatusID, StatusName)

Expected result

I want to display number of Persons whose highest StatusID reached at the start of the month:

September I have 0 since there is no data before the start of month (September 1st)

October: 1 Person has reached StatusID 10 and 20 in september (before October 1st), but I only keep the highest so it's 0 for StatusID10 and 1 for StatusID 20

November: 1 more Person reached StatusID 20 in October ((before November 1st), so total Persons = 2

December: 1 Person moved from 20 to 30 in november

I know how to get number of Persons for each StatusID at the start of month, it's something like:

Number Persons Start = 
VAR StartDate = MIN(Dates[Date])
VAR N = CALCULATE([Number Persons], 'Dates'[Date] < StartDate, ALL(Dates))  
RETURN N

 The part I don't get is how to include only the highest StatusID reached for each Person at the start of the month.

 Many Thanks!

[Icey]

Hi @Anonymous ,

I have a question: For example,

for November, 1 more Person reached StatusID 20 in October ((before November 1st), so total Persons = 2.

If just for November, shouldn't the total persons be 1? So, I count all people before the month of the current row.

Is this what you want?

I delete the relaationship between Dates table and PersonStatus table.

PBIX file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey , thanks for your reply.

Let's clarifiy a little bit. My intermediate table would be calculate the Max StatusID reached by Person at the start of month:

Next, I want to Count total by month from this table:

One person can only be counted once in every month, since they can only have one Highest Status Reached.

Ideally, the measure would work with any time dimension (by month, week or year).

[Anonymous · Accepted solution]

After few hours of trials and errors, I found the solution!

Active Persons = 
VAR StartDate = MIN ( Dates[Date] )
VAR CurrentStatus = MAX ( 'Status'[StatusID] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                CALCULATETABLE (
                    'Persons status',
                    'Persons status'[Date] < StartDate,
                    ALL ( Dates ),
                    ALL ( 'Status' )
                ),
                'Persons status'[PersonID],
                "StatusIDReached", MAX ( 'Persons status'[StatusID] )
            ),
            [StatusIDReached] = CurrentStatus
        )
    )

Basically,  I create a Table with all data prior to the Start of month, then Group By Person with Max(StatusID).
Then, I filter that table to keep only rows where the Highest StatusID reached equals to the variable.

It works for any Date dimension. Pretty happy about it!