Power BI: Find Last Inactive Period of 365 Days

1
votes

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

There are two tables:

  • A Login Table with non-distinct users and their multiple login dates.
  • A User Table with distinct users and their last login dates. I would also like this Reactivation Date to be placed here.

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.

1
powerbidax

1 Answers

1
votes

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!