topic Re: show the list of users of didnot log in last30 days, last 60 days and last 6 months in Developer

show the list of users of didnot log in last30 days, last 60 days and last 6 months

my11 — Wed, 29 Jul 2020 12:33:54 GMT

Hi,

I am new to power BI, and I am struggling with this requirement if anyone can help.

I need to show below 4 queries from my data:

1.Who has not logged in last 30 days?

2.Who has not logged in past 60 days?

3. who has not logged in past 6 months?

4. If never logged in, action date should be blank

I do have an column actiondate which has the log in date of users.

Please suggest how can i do this?

Thank you so much

Re: show the list of users of didnot log in last30 days, last 60 days and last 6 months

Greg_Deckler — Wed, 29 Jul 2020 12:49:19 GMT

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: show the list of users of didnot log in last30 days, last 60 days and last 6 months

Anonymous — Wed, 29 Jul 2020 13:16:25 GMT

how about this

Column =
VAR _datediff =
    DATEDIFF ( 'Table'[Date], TODAY (), DAY )
RETURN
    IF (
        _datediff > 180,
        "Not logged in for 180 days",
        IF (
            _datediff > 61,
            "Not logged in for 60 days",
            IF (
                _datediff > 30,
                "Not logged in 30 days",
                IF ( _datediff < 31, "Logged in in the last 30 days", ( "null" ) )
            )
        )
    )

Re: show the list of users of didnot log in last30 days, last 60 days and last 6 months

Anonymous — Wed, 29 Jul 2020 13:28:45 GMT

Column 2 = VAR _Datediff = DATEDIFF('Table'[actiondate],TODAY(),DAY) Return SWITCH( TRUE(), _Datediff > 180, "Not logged in for 180 days", _Datediff > 61, "Not logged in for 60 days", _Datediff > 30, "Not logged in for 30 days", _Datediff < 31, "Logged in in the last 30 days", "null" )

Try this.

Re: show the list of users of didnot log in last30 days, last 60 days and last 6 months

my11 — Wed, 29 Jul 2020 16:44:00 GMT

Hi @Anonymous

Thank you so much.

It worked but one issue is there, when the actidate is blank(null value), then it is setting up to "Logged in last 30 days" and that is not correct.
Is it possible to set it to "never logged in" when the action date is null?

ry Re: show the list of users of didnot log in last30 days, last 60 days and last 6 months

Anonymous — Wed, 29 Jul 2020 17:24:45 GMT

Try the following

Column 2 = VAR _Datediff = DATEDIFF('Table'[actiondate],TODAY(),DAY) Return SWITCH( TRUE(), _Datediff < 1, "Never logged in", _Datediff > 180, "Not logged in for 180 days", _Datediff > 61, "Not logged in for 60 days", _Datediff > 30, "Not logged in for 30 days", _Datediff < 31, "Logged in in the last 30 days", "null" )

If it works, Please accept the solution and drop me a thumbs up.

Karlos O'Neill.

Re: show the list of users of didnot log in last30 days, last 60 days and last 6 months

Anonymous — Wed, 29 Jul 2020 18:08:31 GMT

@my11

I've improved it, to also account for people who have logged in on the same day.

Column 3 = VAR _Datediff = DATEDIFF('Table'[actiondate],TODAY(),DAY) Var _dayspassed = SWITCH( TRUE(), _Datediff > 180, "Not logged in for 180 days", _Datediff > 61, "Not logged in for 60 days", _Datediff > 30, "Not logged in for 30 days", _Datediff < 31, "Logged in in the last 30 days", "Null" ) Return IF(LEN('Table'[Column])=BLANK(),"never logged in",_dayspassed)