Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have data like below. If you see the required output table below I need to show years with start and end data points. I can't supress zeros becuase I need to show Produts 5, 6.
Dimension: Year
Measure: Sum(Sales)
Prtoduct | Sales | year |
1 | 2009 | |
2 | 100 | 2010 |
3 | 300 | 2011 |
4 | 500 | 2012 |
5 | 2013 | |
6 | 2014 | |
7 | 600 | 2015 |
8 | 2016 | |
9 | 2017 | |
required output | ||
Prtoduct | Sales | year |
2 | 100 | 2010 |
3 | 300 | 2011 |
4 | 500 | 2012 |
5 | 2013 | |
6 | 2014 | |
7 | 600 | 2015 |
I suppose you need to show years 1,8 and 9 as well? Im not seeing this in your output table.
If you just add the Sum(Sales) as a measure and Prtoduct and Year as a dimension doesn't this give you the desired result?
Regards Jandre
Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn
@JandreKillianRIC , I need to show all years between two points where data exixts. Just like required output table above.
Try this
If(Year = Min({1} Year) OR Year = Max({1} Year), Sum(Sales))
An alternative approach is
If(Year = Min(Aggr(If(Sales>0, Year), Product))
OR
Year = Max(Aggr(If(Sales>0, Year), Product)),
Sales
)
This ensures that for each Product, only the first and last year with sales are displayed.