older than 3 months measure

[JENS]

I need to find employees that havent been qualifyed testet in the last 3 month. / who has a latest test  date older than 3 month.

Table: dw Dim_Employee    field: Employee_employeeID

Table: dw Fact_Quality   field: DATEREG

hope some can help

br. Jens

[v-yulgu-msft · Accepted solution]

Hi @JENS,

Please add a calculated column which returns month interval between latest test date and today for each employee.

Days =
DATEDIFF (
    CALCULATE (
        MAX ( 'dw Fact_Quality'[DATEREG] ),
        ALLEXCEPT ( 'dw Fact_Quality', 'dw Fact_Quality'[Employee] )
    ),
    TODAY (),
    MONTH
)

Then, use this measure to count the number of employee who haven't been qualified tested in the last 3 month.

older than 3 month =
CALCULATE (
    DISTINCTCOUNT ( 'dw Fact_Quality'[Employee] ),
    FILTER ( 'dw Fact_Quality', 'dw Fact_Quality'[Days] > 3 )
)

Best regards,

Yuliana Gu