Forum Discussion
Multiple month columns to align for chart
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
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
5 Replies
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)
Do you have any Date column in Excel Sheet? Anonymous
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....
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
