Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 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
    7 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
    7 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
Icon for Community Champion rankCommunity 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,