I think you'll just need an 'if/then' statement to get the answer you're looking for. It might look something like this:
If Year < 2016 and SUM([Hires]) > 0 Then 'Old'
This will likely have to be the second column after [Company] since it will be a dimension, but it should otherwise look like your example.
Let me know if you have any questions.
Wesley's solution should work once you pivot the year columns(better approach).
If you didn't want to pivot, you can use below calc:
ZN()+ZN()+ZN()+ZN()=0 THEN 'New'
Seems like Year is a string, if it is use int([Year]) instead of Year. And use countd() instead of Sum for fellow.
Sadly it's still not working exactly how I hoped. As you can see in the screenshot, whenever a company does have a record for 2016 AND records before 2016 it's splitting the company into two (new and old). However, "Azavea" for example, isn't actually a New company, they've actually hired 7 between 2014 and 2015.
Bump! Can anyone help please?
You can try this calculation:
In your case though, you have to replace DATEPART('year',[Order Date])=2015 with Int([Year])=2016
And if you're going to get more data over time, you can consider using the calc below to make your dashboard more efficient (instead of using = 2016):
Int([Year]) = DATEPART('year', today())
Hope this helps!
Hi Lisa - Thanks so much for your help! Tried your formula, but it's still splitting certain records into 2 rows - I don't think it's correctly reading rows that have records in previous years as well as 2016.
Here's a screenshot where I've highlighted two companies (Astronomer and Azavea) where you can see what's happening. Any thoughts? I've attached the workbook too (sheet 17). I've been struggling with this one all weekend and can't seem to get it to work!
2015-2016 Dashboard.twbx 2.0 MB
It works! Thanks so much, Lisa!!