Multiple month columns to align for chart

[Anonymous]

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]

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.

[PattemManohar]

@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.... 

[chotu27]

Do you have any Date column in Excel Sheet? @Anonymous

[Anonymous · Accepted 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

[PattemManohar]

@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)