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.
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.
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 :).