Display active years of products based on start and end date

[Anonymous]

Hi, I am trying to solve a business question and not sute how to solve this.

I have the following data:

Product	Start Date	End Date
Apple 4S	7/1/2007	3/15/2013
Apple 5S	8/1/2009	4/13/2012
Apple 6S	6/27/2010	9/11/2015
Apple 8S	11/13/2013
Apple 10S	3/23/2016

We would like to know for each product, the years it was active (with a Y - for active). Sample output:

Product

2007

2008

2009

2010

2011

2012

2013

2014

2015

2016

2017

2018

2019

Apple 4S

Y

Y

Y

Y

Y

Y

Y

Apple 5S

Y

Y

Y

Y

Apple 6S

Y

Y

Y

Y

Y

Y

Apple 8S

Y

Y

Y

Y

Y

Y

Y

Apple 10S

Y

Y

Y

Y

Y

Can you please help on how can we achieve this?

Thanks and Regards,

SN

[Anonymous]

@Anonymous - 

It looks like @Ashish_Mathur  has a good solution. Here is another one:

1. Create a Year Calculated Table in DAX:

Year = 
var firstyear = YEAR(MIN('Product Dates'[Start Date]))
var lastyear = YEAR(TODAY())
return SELECTCOLUMNS(GENERATESERIES(firstyear, lastyear, 1),"Year",[Value])

2. Create a Product Years Calculated Table in DAX:

Product Years = 
var yearend = YEAR(TODAY())
var cross = CROSSJOIN('Product Dates','Year')
return FILTER(cross,NOT(OR([Year] < YEAR([Start Date]),[Year] > IF(ISBLANK([End Date]),yearend,YEAR([End Date])))))

3. Create a relationship between the 2 new Calculated Tables.

4. Create a Measure in DAX:

Product Active = IF(COUNTROWS('Product Years') = 0, BLANK(),"Y")

5. In your Matrix Visual, add Year from the Year table, Product from the Product Years table, and the Product Active measure.

Hope this helps,

Nathan

[Ashish_Mathur]

Thank you @Anonymous 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks @Ashish_Mathur  for your help. The approach you have provided gives me what I am looking for but unfortunately I may not be able to implement this as it won't be scalable when I apply this on my actual data for the following reasons:

I have many products(few thousands) which were/are active across many years, which makes the data table you proposed to be huge(record for each product-for each day-for all the years the product is active). This table could end up with millions of rows.

I was wondering, instead of creating 1 row per day, can we just create 1 row per year per product ? So that it is manageable. Something like this :

Product          Date 

Apple 4S      7/1/2007

Apple 4S      7/1/2008 --Any date in this year will do. Better yet if we can just have the year

Apple 4S      7/1/2009

Apple 4S      7/1/2010

Apple 4S      7/1/2011

Apple 4S      7/1/2012

Apple 4S      7/1/2013

I am not sure how to create this automatically.

I was also wondering how did you create the Data table. If this is created manually, it may not work as I want the years a product is active calculated automatically.

Your help and expertise are greatly appreciated.

Thanks a bunch,

SN

[Ashish_Mathur · Accepted solution]

Hi,

I have condensed the Data Table to one row per product per year.  The end result stays the same.  Download the PBI file from here.

Hope this helps.

[Anonymous]

Thanks so much for your help. Power BI communities role is pivotal to the success of PBI and users like you and many more are making this happen. Three cheers to the awesome community. 

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.