Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 -
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
Thank you @Anonymous
Hi,
You may download my PBI file from here.
Hope this helps.
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
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.
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.
You are welcome. Thank you for your kind words.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |