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 All,
I have table as below and I need to have a graph for last 13 months (Ex: Oct2017 to Oct 2018), can any one help hpw to arrange data to build stacked bar or line graph.
Product | Total Region=Jan 2017Actual Results | Total Region=Feb 2017Actual Results | Total Region=Mar 2017Actual Results | Total Region=Apr 2017Actual Results | Total Region=May 2017Actual Results | Total Region=Jun 2017Actual Results | Total Region=Jul 2017Actual Results | Total Region=Aug 2017Actual Results | Total Region=Sep 2017Actual Results | Total Region=Oct 2017Actual Results | Total Region=Nov 2017Actual Results | Total Region=Dec 2017Actual Results | Total Region=Jan 2018Actual Results | Total Region=Feb 2018Actual Results | Total Region=Mar 2018Actual Results | Total Region=Apr 2018Actual Results | Total Region=May 2018Actual Results | Total Region=Jun 2018Actual Results | Total Region=Jul 2018Actual Results | Total Region=Aug 2018Actual Results | Total Region=Sep 2018Actual Results | Total Region=Oct 2018Actual Results |
A | 99.98% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.98% | 99.15% | 100% | 100% | 100% | 99.98% | 99.15% | 100% | 99.98% | 99.98% | 100% |
B | 99.98% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.98% | 99.15% | 100% | 100% | 100% | 99.98% | 99.15% | 100% | 99.98% | 99.98% | 100% |
C | 99.98% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.98% | 99.15% | 100% | 100% | 100% | 99.98% | 99.15% | 100% | 99.98% | 99.98% | 100% |
D | 99.98% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.98% | 99.15% | 100% | 100% | 100% | 99.98% | 99.15% | 100% | 99.98% | 99.98% | 100% |
E | 99.98% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.98% | 99.15% | 100% | 100% | 100% | 99.98% | 99.15% | 100% | 99.98% | 99.98% | 100% |
F | 99.98% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.15% | 99.15% | 100% | 99.98% | 99.98% | 99.15% | 100% | 100% | 100% | 99.98% | 99.15% | 100% | 99.98% | 99.98% | 100% |
Thanks in advance.
MM
Solved! Go to Solution.
@Anonymous,
Unpivot your columns, then create YearMonth, MonthName, MonthNumber column in Power BI Desktop query Editor.
After applying changes to report view, create the following columns in your table.
Date = DATE(Table1[Year],Table1[MonthNumber],1)
LAST13MONTH = DATEDIFF(Table1[Date],TODAY(),MONTH)<=12
Create a line chart, drag the LAST13MONTH collumn to visual level filter and set its value to True. For more details, please review attached PBIX file.
Regards,
Lydia
Hi All,
I have data in Excel, but once I have get uploaded in PBI the PBI should consider only last 14 months data from the table.
Any suggestion or help will be great.
Thanks in advance.
Murali.
@Anonymous Please use the "Power Query" to filter the last 14 months data, use "Custom Filter" on the date field that you want to filter on....
Proud to be a PBI Community Champion
Do you have any Date column in Excel Sheet? @Anonymous
@Anonymous,
Unpivot your columns, then create YearMonth, MonthName, MonthNumber column in Power BI Desktop query Editor.
After applying changes to report view, create the following columns in your table.
Date = DATE(Table1[Year],Table1[MonthNumber],1)
LAST13MONTH = DATEDIFF(Table1[Date],TODAY(),MONTH)<=12
Create a line chart, drag the LAST13MONTH collumn to visual level filter and set its value to True. For more details, please review attached PBIX file.
Regards,
Lydia
@Anonymous Could you please try, unpivoting all the MonthYear Fields and then have two fields as "Month" and "Year". Now, you have data in more structured format. Then you can use Year and Month fields to create a hierarchy for each product (which you can drill-through)
Proud to be a PBI Community Champion
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 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |