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

[my11]

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

[Anonymous · Accepted solution]

@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)

[Anonymous]

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. 

[Anonymous]

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" ) )
            )
        )
    )

[my11]

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?

[Anonymous]

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. 

[Greg_Deckler]

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.