Forum Discussion

Anonymous9999's avatar
Anonymous9999
New Member
3 years ago
Solved

Taking Distinct count after calculating a measure

I have a database for students. Student name, school joining date, teacher assigned, Date of termination, expected graduation date. Here since I have to take count for every date column by month, I...
  • FreemanZ's avatar
    3 years ago

    hi Anonymous9999 

     

    try to create plot a table visual with the measure like this:

    Result = 
    VAR CurrentMonth = MONTH(MAX(TableName[Joining Date]))
    RETURN
    CALCULATE(
        DISTINCTCOUNT(TableName[Teacher]),
        FILTER(
            ALL(TableName),
            MONTH(TableName[Joining Date])<=CurrentMonth            
        )
    ) -
    CALCULATE(
        DISTINCTCOUNT(TableName[Teacher]),
        FILTER(
            ALL(TableName),
            OR (
                AND ( 
                    MONTH(TableName[Terminated Date])<>BLANK(), 
                    MONTH(TableName[Terminated Date])=CurrentMonth
                ),
                AND( 
                    MONTH(TableName[Graduated Date])<>BLANK(), 
                    MONTH(TableName[Graduated Date])=CurrentMonth
                )
            )
        )
    )

     

    i tried and it worked like this:

     

    the dataset: