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

- calendar table

- members table : MemberID,

                             Registration Date,

                             Termination Date,

 i.e: 

MemberIDRegistered DateTermination Date
101/01/2018 
212/05/201810/09/2018
302/03/2019 
401/06/201930/07/2019
515/09/201815/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-19Feb-19Mar-19Apr-19May-19  …
Total5945516865   …

 

 

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'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

10 Replies

  • 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

    • 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,

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

    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