How to get date difference between last logon and last password reset dates in Active Directory

[KeithKing]

Hi Guys, I would like to get the date difference between Last Logon and Last Password Reset columns and the display a warning or text that the user hasn't reset their password in x amount of days.

This is what I am using now but does not display the correct information:

     Measure =
VAR Created =
MAX ( user[user.lastLogon].[Year])
VAR Closed =
MAX ( user[user.pwdLastSet].[Year] )
VAR NoOfDays =
IF (
Closed > Created,
DATEDIFF ( Created, Closed, YEAR),
DATEDIFF ( Closed, Created, YEAR)
)
RETURN
IF ( NoOfDays >= 1, "User account Fine", "Password Needs to be Reset )

[az38]

Hi @KeithKing 

try the same but without .[Year] in Created and Closed variables

That should be enough

do not hesitate to give a kudo to useful posts and mark solutions as solution

[KeithKing]

Hi,

It sort of works now 🙂  but the problem is that I have an account CustomerCare1  with last logon at 2019/12/06 and last password reset at 2019/08/02.  This is a 4 month difference but it says the user account is fine? It needs to check the year as well as months (3 months after that password needs to be reset)

Hope I am making sense?

Thanks

[az38]

@KeithKing 

DATEDIFF ( Created, Closed, MONTH) will give you difference in monthes without any dependencies of Year

For example 

DATEDIFF ( DATE(2019,11,1), DATE(2020,1,11), MONTH) will return you 2 month difference

do not hesitate to give a kudo to useful posts and mark solutions as solution

[KeithKing]

Thanks!! 

[az38]

@KeithKing 

does it work ok? 

do not hesitate to give a kudo to useful posts and mark solutions as solution