Active Members per Month

[Anonymous]

Hi there,

Looking around on the different posts regarding this type of calculation, I couldn't find anything that helps with my case. So hopefully someone would be able to help.

I have 2 tables:

- calendar table

- members table : MemberID,

                             Registration Date,

                             Termination Date,

 i.e: 

MemberID	Registered Date	Termination Date
1	01/01/2018
2	12/05/2018	10/09/2018
3	02/03/2019
4	01/06/2019	30/07/2019
5	15/09/2018	15/02/2019

There are 2 relationships created:

- Between Date in Calendar table and Registration Date in members table

- Between Date in Calendar table and Termination Date in members table

I need to create a matrix showing the number of active members per month. Something like this:

Number of Active Members per Month
	Jan-19	Feb-19	Mar-19	Apr-19	May-19	…
Total	59	45	51	68	65	…

All the DAX query created so far haven't worked and I am now running out of idea, so any help or thoughts would be very much appreciated.

Thank you

[AlB · Accepted solution]

Hi @Anonymous 

Try this:

1. Delete the relationships

2. Place Calendar[MonthYear] in the rows of a table visual

3. Create this measure

Measure =
VAR AuxTable_ =
    FILTER (
        Table1,
            NOT(
             Table1[Registered Date] > MAX ( Calendar[Day] )
                || (
                    NOT ISBLANK ( Table1[Termination Date] )
                        && Table1[Termination Date] < MIN ( Calendar[Day] )
                )
            )
    )
RETURN
    COUNTROWS ( AuxTable_ )

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[AlB]

@Anonymous 

I'm afraid I don't understand anything of what you just said. If you provide clear examples explaining the expected result it will be easier to follow. Otherwise you can have a good look at the code and make the necessary modifications for your case.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

I don't know why I can't reply with my sample here. So, I posted a topic here: Status change and count - Microsoft Power BI Community

[AlB]

@Anonymous 

You don't need COUNTA to exclude blanks, COUNT also excludes them:

Measure =
VAR AuxTable_ =
    FILTER (
        Table1,
        NOT (
            Table1[Registered Date] > MAX ( Calendar[Day] )
                || (
                    NOT ISBLANK ( Table1[Termination Date] )
                        && Table1[Termination Date] < MIN ( Calendar[Day] )
                )
        )
            && Table1[Reason for joining] = "Losing weight"
    )
RETURN
    COUNTX ( AuxTable_, Table1[Registered Date] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Yes, it works when I filter it by date column. But when I clear the filter it counts rows with exit date as well. I have 4 dates for 4 status to track: Referred date, accepted date, enroll date and exit date. So, every referred date without accepted date counted as 'Referred', accepted date without enroll date counted as 'Accepted',  enroll date without exit date counted as 'Active', and enroll date with exit date counted as 'Exited'. Can I achieve this? Thanks,

[AlB · Accepted solution]

Hi @Anonymous 

Try this:

1. Delete the relationships

2. Place Calendar[MonthYear] in the rows of a table visual

3. Create this measure

Measure =
VAR AuxTable_ =
    FILTER (
        Table1,
            NOT(
             Table1[Registered Date] > MAX ( Calendar[Day] )
                || (
                    NOT ISBLANK ( Table1[Termination Date] )
                        && Table1[Termination Date] < MIN ( Calendar[Day] )
                )
            )
    )
RETURN
    COUNTROWS ( AuxTable_ )

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Anonymous]

The measure seems to work for my case too, but I want result from the counta of the start date (registation date column) instead of counting all rows. But, when I change return to counta, it didn't work and this message pop up: The COUNTA function only accepts a column reference as an argument. I have blank rows on my on my start date that is why I want to count only rows with value. Appreciate if you can help me fix it. Thanks,

[Anonymous]

Thanks AIB for your quick reply.

Actually, the measure seems working =D you're the best!, I forgot to mention that there is another filter to add which should only count the members with a Reason for joining = "Losing weight" this is another colunm on the Members table.

How can we had this filter to the measure?

May I also ask you about the measure, what it does exactly, I'm not sure I understand it completely and I'm surprised we had to remove the relationships. I thought the measure needed to be based on the distinctcount of MembersID, Registration date and Termination Date.

Thank you again 

[AlB · Accepted solution]

@Anonymous 

Glad to hear it works. I hadn't tested it. We can just add the new condition to the filter:

Measure =
VAR AuxTable_ =
    FILTER (
        Table1,
        NOT (
            Table1[Registered Date] > MAX ( Calendar[Day] )
                || (
                    NOT ISBLANK ( Table1[Termination Date] )
                        && Table1[Termination Date] < MIN ( Calendar[Day] )
                )
        )
            && Table1[Reason for joining] = "Losing weight"
    )
RETURN
    COUNTROWS ( AuxTable_ )

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[AlB]

@Anonymous 

With a relationship you wouldn't be able to filter for a period determined by two different columns. If you select a month in your calendar table you could identify the members that registered or terminated on that month but would be missing out all the rest. Can you think of a way to do it with relationships?

The calendar table (with no relationships) can in any case be used for selecting the periods you want to look at in your measure, without interfering in your main table. What the measure does is just check, row by row, which members were registered in the selected period. IF a member terminated before the period started or registered after the period ended, we discard them. All the rest are in some way active in the period. That's what this condition states and it's the crux of the measure:

NOT (
            Table1[Registered Date] > MAX ( Calendar[Day] )
                || (
                    NOT ISBLANK ( Table1[Termination Date] )
                        && Table1[Termination Date] < MIN ( Calendar[Day] )
                )
        )

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Anonymous]

Hi @AlB ,

Thanks a lot for taking the time to explain the measure, it makes more sense.

The measures I was trying before were just not working because I was using one relationship to filter the Registration date and then the other relationship to filter the Termination Date.

Thanks a lot for all your help, it's very much appreciated 🙂