cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jdugre Frequent Visitor
Frequent Visitor

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

I have a good challenge here.

My data looks like

PersonStatus:

1.png

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:

2.png

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!

1 ACCEPTED SOLUTION

Accepted Solutions
jdugre Frequent Visitor
Frequent Visitor

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

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!

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

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

Hi @jdugre ,

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?

number.PNG

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.

jdugre Frequent Visitor
Frequent Visitor

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

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:

3.pngNext, I want to Count total by month from this table:

4.png

 

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).

 

 

 

jdugre Frequent Visitor
Frequent Visitor

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

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!

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors