Referencing Specific Columns in a Calculated Column Measure

Ahh, that makes a TON more sense, and can definitely be done within PowerBI.  

 

To set these up as calculated columns, you can use measures like these.  I'm unsure if you have a Users table, or just the one User/Revenue/Date table.  Best practice would be to have a Users table related to this one (which I'm calling Revenue), so that's what I'm going to write this like you're adding columns to the Users table.

Usage Status = 
VAR mostRecentDate =MAX(Revenue[Revenue Date])
RETURN

IF( 
    CALCULATE( SUM(Revenue[Revenue Amount]), DATESINPERIOD(Revenue[Revenue Date], mostRecentDate, -1, YEAR) ) > 0, 
    "Active", "Inactive" 
)

 

The Classification column gets a bit trickier, since you want to reset if there's a 12 month gap.  I'm going to step through my thought process in creating this column. Here's the basic "first date with an entry in the revenue table" version:

Classification = CALCULATE( MIN(Revenue[Revenue Date]), FILTER(Revenue, Revenue[User name] = EARLIER(Users[User name]) && Revenue[Revenue Amount] > 0))

 

Now we need to go through and find a way to identify gaps and return the most recent date of positive revenue after a gap.  There may be a better/more efficient way to do this, but here's the method I would start with.  First, we need to get a list of all dates with positive revenue for the user. SUMMARIZE is a pretty efficient way to do that.

SUMMARIZE( 
        FILTER(Revenue, Revenue[Revenue amount] > 0 && Revenue[User Name]=EARLIER(Users[User Name])),
        Revenue[Revenue Date],
    "First After Gap", VAR CurDate = MAX('Revenue'[Revenue Date]) RETURN
        CALCULATE( SUM(Revenue[Revenue Amount]), ALLEXCEPT(Revenue, Revenue[User Name]), Revenue[Revenue Date]<CurDate ) = 0)

Now we need an indicator on this virtual table to indicate which entries are the first after 12 month breaks.  Using ADDCOLUMNS with the table we just created can get us there.  I'm assuming your date entries are of type datetime, and you're just displaying it in MMM-yy format.  If not, you'll have to write your own custom date comparison logic.

SUMMARIZE( 
        FILTER(Revenue, Revenue[Revenue amount] > 0 && Revenue[User Name]=EARLIER(Users[User Name])),
        Revenue[Revenue Date],
    "First After Gap", VAR CurDate = LASTDATE('Revenue'[Revenue Date]) RETURN
        CALCULATE( SUM(Revenue[Revenue Amount]), ALLEXCEPT(Revenue, Revenue[User Name]), Revenue[Revenue Date]<CurDate && Revenue[Revenue Date] > CurDate-365 ) = 0)

Then we want to filter that where First After Gap is true, and get the maximum date from that list.  We end up with this measure:

Classification = 
MAXX(
    FILTER(
        SUMMARIZE( 
            FILTER(Revenue, Revenue[Revenue amount] > 0 && Revenue[User Name]=EARLIER(Users[User Name])),
            Revenue[Revenue Date],
            "First After Gap", 
            VAR CurDate = LASTDATE('Revenue'[Revenue Date]) RETURN
            CALCULATE( 
                SUM(Revenue[Revenue Amount]), 
                ALLEXCEPT(Revenue, Revenue[User Name]), Revenue[Revenue Date]<CurDate && Revenue[Revenue Date] > CurDate-365 )
            = 0
        ), 
    [First After Gap]), 
[Revenue Date])

It's not pretty, and I'm almost positive there are cleaner ways to handle the rolling sum calculation (a date dimension would be very useful here), but it gets you the right result for each use case.