Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Active Members per Month

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 tabl...
  • AlB's avatar
    AlB
    6 years ago

    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  Datanaut

  • AlB's avatar
    AlB
    6 years ago

    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  Datanaut

AlB's avatar
AlB
Community Champion
6 years ago

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  Datanaut

          

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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