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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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 :smileyvery-happy:

    • AlB's avatar
      AlB
      Community Champion
      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
    5 years ago

    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,