CODEWITHSUNDEEP
powerbidax
Sebastian Hubard
Sebastian Hubard

Reputation: 163

Power BI: Find Last Inactive Period of 365 Days

I'd like to find the reactivation date after someone had last been inactive for 365 days.

There are two tables:

enter image description here

I used the following Dax formula to get the "last login date"

Last Login Date = MAXX(RELATEDTABLE('Login Table'), 'Login Table'[Login Date])

Any help would be greatly appreciated.

Upvotes: 1

Views: 511

Answers (1)

user12493039
user12493039

Reputation:

One way to solve this is by using multiple measures and columns.

  1. For your Login Table, add a new calculated column with the following definition:

    Previous Login = CALCULATE(MAX(LoginTable[Login]), FILTER(LoginTable, LoginTable[User] = EARLIER(LoginTable[User]) && LoginTable[Login] < EARLIER(LoginTable[Login])))

This will create a new calculated column in your Login Table which adds the date of the previous login to your table. (My Login Date column is called Login, therefore you might need to change this)

  1. Now, simply calculate the DAY difference between the login date and the previous login date:

Day Difference = DATEDIFF(LoginTable[Previous Login], LoginTable[Login], DAY)

  1. Using a simple IF statement, you can now add a new calculated column returning 1 if this login is a reactivation or not:

Is Reactivated = IF(DATEDIFF(LoginTable[Previous Login],LoginTable[Login], DAY) > 360, 1, 0)

  1. To make things easier, also create a calculated column that returns the login date, if this is a reactivation:

Reactivate = IF(LoginTable[Is Reactivated] == 1, LoginTable[Login], BLANK())

  1. Now you simply have to create a calculated table which groups by user and gets the max login date and max reactivated date like this:

Table = GROUPBY(LoginTable, LoginTable[User], "Last Login", MAXX(CURRENTGROUP(), LoginTable[Login]), "Last Reactivation", MAXX(CURRENTGROUP(), LoginTable[Reactivate]))

A lot of steps could be put into one steps but this way, it is simpler to understand and troubleshoot.

Hope this helps!

Upvotes: 1

Related Questions