Referencing Specific Columns in a Calculated Column Measure

[Wkeith]

So basically I want to transfer a current forumla that I have in Excel into Power BI. However, I haven't been able to figure out how to do so. Here is the current logic in Excel:

This just takes each user's revenue and if it's above 0 it classifies as 1 and if its not its 0. Also broken out by each quarter. 

I then have a forumla that classifies each user based on the sum of these 1s and 0s to put each user into a classification bucket (what you see in the "User Definition" Column). Here is the formula but basically just a super long IF statement for each quarter: 

=IF(SUM(AR12:BB12)=0,"Pre-2016 Revenue",IF(SUM(AR12:BB12)=11,"Current User",IF(SUM(AS12:BA12)=9,"Current User",IF(AND(SUM(AT12:BA12)=0,BB12=1),"New User Q2 2019",IF(AND(SUM(AT12:AZ12)=0,BA12=1),"New User Q1 2019",IF(AND(SUM(AT12:AY12)=0,AZ12=1),"New User Q4 2018",IF(AND(SUM(AT12:AX12)=0,AY12=1),"New User Q3 2018",IF(AND(SUM(AT12:AW12)=0,AX12=1),"New User Q2 2018",IF(AND(SUM(AT12:AV12)=0,AW12=1),"New User Q1 2018",IF(AND(SUM(AS12:AU12)=0,AV12=1),"New User Q4 2017",IF(AND(SUM(AS12:AT12)=0,AU12=1),"New User Q3 2017",IF(AND(SUM(AR12:AS12)=0,AT12=1),"New User Q2 2017",IF(AND(AS12=1,SUM(AT12:BB12)=0),"Lost User",IF(AND(AR12=1,SUM(AS12:BB12)=0),"Lost User",IF(AND(AS12=1,SUM(AZ12:BB12)=0),"Lost User","Spotty User")))))))))))))))

Is there a way to transfer this above logic into a calculated column so I can sort my report based on it? I don't want it to be a measure because you can't sort a report based on a measure. Here is what I have so far but I want to get this into a calculated column: 

Thanks for the help! 

[Cmcmahan]

That Excel formula hurts to look at.  Let's break it down into human readable conditions.  These are in priority order, so the first condition that applies is the category the user gets.

• If the user has no (positive) revenue between 2016 and now, their category is "Pre-2016 Revenue"
• If a user has been active for all quarters since 2016, they are a "Current User"
• If a user has been active for all quarters between 2017Q1 and 2019Q1, they are a "Current User" (?)
• If a user's has any revenue starting from a certain date, they are a "New User <First Quarter w/ Revenue>"
  • This starting date is usually 2017Q2, but changes to 2017Q1 for the 2nd half of 2017, and to 2016 for the first half of 2017.(??) There is no option for "New User Q1 2017" (?)
• If a user has revenue in 2017Q1 and no revenue from 2017Q2-present, they are a "Lost User"
• If a user has revenue in 2016 and no revenue from 2017Q1-present, they are a "Lost User"
• If a user has revenue in 2017Q1 and no revenue from 2018Q4 though 2019Q2, they are a "Lost User" (???)
• Otherwise, they are a "Spotty User"

This whole setup seems like it has a lot of holes in it. I'm pretty sure that Spotty User is only possible if they had revenue in 2017Q1 and have at least one month without revenue since.  The 3rd Lost User condition doesn't seem possible to reach either. It seems like this entire logic can be cleared up a LOT.  

Could you tell us in a human-readable format what the conditions for the categories are?  We can write DAX for this as a calculated column, but let's not waste time doing it for a flawed set of conditions.

[Wkeith]

Thanks for the help @Cmcmahan. I have gone back and changed the logic in my Excel file to be more coherent (I was given this file so I had no say in that original formula). Basically, as you can see in the picture below, I want two seperate classifications. The first, "Classification", is the original start of that user. However, there is a slight twist to this. As you can see in User 3 their classification returns "Mar-20" because in my forumla I have set it up so that if a user dosen't have any revenue 12 months after their last revenue month (I.E. Mar-19 to Feb-20) then reclassify this user once he does revenue again. In this case, it happens to be Mar-20. 

The second classifier, "Usage Status", is just simple forumla that looks in the past twelve months (in this case Apr-19 to Mar-20) to see if a User's revenue is greater than 0. If it is, then return "active" otherwise return "inactive." 

Can you make both of these classifications as calculated columns in Power BI? The way my data is set up in Power BI is not like Excel. In Power BI, I will be refrencing a sales table that follows a layout like the screenshot below (more of a columnar format rather than a crosstab format in my example above):

Appreciate all the help on this. Let me know if you need more info! 

[Cmcmahan · Accepted solution]

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. 

[Wkeith]

@Cmcmahan You are a god. Thank you so much for all the help, truly a life saver. Hope you have a great weekend sir! 

[Cmcmahan]

I'm not surprised you had trouble figuring it out.  It's actually pretty difficult to get a rolling 12 month total in a virtual table, since you can't use DATEADD during SUMMARIZE or ADDCOLUMNS.  I highly suspect that the calculation becomes much simpler with the addition of a date dimension, since you can then use normal time intelligence functions.

[Wkeith]

When you refer to a "date dimension" is that just a date table within my Power BI File? I already have a master date table, DimDate, that I refrence in other measures and it is connected to my Sales table, FactSales, which is also connected to my users table, DimUsers. I forgot to mention all of this in my orginal post. 

Would you mind explaining how I could use a date dimension to make this easier? Just for my own learning purproses :). 

[Cmcmahan]

That's exactly what I mean.  The dim in dimDate is short for dimension.  The big benefit is being able to use DATESINPERIOD without also using a variable to hold the current date.  So this gives the same result as before.

Usage Status dimDate = 
IF( 
    CALCULATE( SUM(Revenue[Revenue Amount]), DATESINPERIOD(dimDate[Date], MAX(Revenue[Revenue Date]), -1, YEAR) ) > 0, 
    "Active", "Inactive" 
)

It doesn't seem that impressive, here, but it becomes more useful with the Classification.

Classification with dimDate =
MAXX (
    FILTER (
        ADDCOLUMNS (
           VALUES(Revenue[Revenue Date]),
            "First After Gap", CALCULATE (
                SUM ( Revenue[Revenue Amount] ),
                ALL ( Revenue[Revenue Date] ),
                DATESINPERIOD ( dimDate[Date], [Revenue Date] - 1, -1, YEAR )
            )
        ),
        ISBLANK ( [First After Gap] )
    ),
    [Revenue Date]
)

This gives us the ability to avoid using mid-expression variables, and allows us to use DATESINPERIOD instead of specifically calculating start and end dates.  It also allows the use of ADDCOLUMNS instead of SUMMARIZE syntax, which is best practice when using grouping with DAX. I actually spent a lot of time trying this method originally (since that's how I usually do it), before realizing that it required a date dimension to actually work against.  Since your date column doesn't have an entry for every date, a lot of them time intelligence functions break down.

[Cmcmahan]

Ah, my apologies.  I only used test data of people that had at least one payment somewhere in their history.  Good job on figuring it out!

[Wkeith]

@Cmcmahan Thanks again for the explanation. Is there a way to get users who have never had revenue to just display blank instead of the current month (I.E. August 2019)?I'm getting a lot of users displaying August 2019 in my DimUsers table because they have not done revenue yet. This wasn't an issue in your orginal code but when I switched to the newest more efficient code it caused this issue. See below: 

[Wkeith]

Just in general, I don't think the new forumla is working. It is giving me much different results than the original formula you posted about. Not sure the root cause of it but just thought I would let you know. It appears that the new calculated column is marking a lot of the users as "August 2019" when in reality they have revenue much before that. Any idea why it would be deafulating to this for some users?

Examples: 

[Wkeith]

I have resolved the issue. I needed to add back in this part of the orignal solution into the new solution in order for it to work like before: 

FILTER(
        SUMMARIZE( 
            FILTER(Revenue, Revenue[Revenue amount] > 0 && Revenue[User Name]=EARLIER(Users[User Name])),
            Revenue[Revenue Date],
 

[Wkeith]

Bump. 

[Wkeith]

@jdbuchanan71 @Cmcmahan