topic Re: How to get date difference between last logon and last password reset dates in Active Directory in DAX Commands and Tips

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

KeithKing — Fri, 07 Feb 2020 05:52:02 GMT

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 )

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

az38 — Fri, 07 Feb 2020 06:26:44 GMT

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

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

KeithKing — Fri, 07 Feb 2020 07:01:49 GMT

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

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

az38 — Fri, 07 Feb 2020 07:30:08 GMT

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

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

KeithKing — Fri, 07 Feb 2020 07:50:24 GMT

Thanks!!

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

az38 — Fri, 07 Feb 2020 07:56:22 GMT

KeithKing

does it work ok?

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