Forum Discussion
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.
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.
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!
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.
Cmcmahan You are a god. Thank you so much for all the help, truly a life saver. Hope you have a great weekend sir!
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.
Bump.