Help with chart and filters

[Anonymous]

Hello All,

I have a list of Products and their prices per month from 2010 till 2020 Dec like below.

Date	AlPT	AlPUST	AlAEURT
Jan-10	1.5	2.5	1.9
Feb-10	2	2.8	2.5
...
Dec-20	9	7.9	5.5

1)How do i get a line chart if i want to compare from maybe may 2014 to june 2015.The plot happens per month then i lose sight of year And if i include year as well in Axis then it just sums the year up.

2)If i upload the file as above format it becomes difficult if i want to have a filter on the product and date range as the products are in different columns.Is there a way to solve this.

Thanks

[v-juanli-msft]

Hi @Anonymous 

amitchandak has pointed the right direction to solve your problems.

If you have any problem inplementing it on your side, please  don't hesitate to ask here.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

You can try using a Slicer to select the year and month specific ranges. 

In order to lose sight of the year when plot needs to be seen by month and vise versa,  Drill down/Drill Up feature in line chart would automatically suffice the need using date hierarchy.

Hope this helps!

Thanks!

[amitchandak · Accepted solution]

@Anonymous 

First, unpivot the table

https://radacad.com/pivot-and-unpivot-with-power-bi

from month Create a date

Date = "1-" & table[Month] based on you format

Join with a date calendar table

There you can create month year and month year sort column

Month Year = format(date[date],"MMM-YYYY")

Month Year Sort = format(date[date],"YYYYMM")

Use those

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

hello @amitchandak 

Thanks for the unpivot solution that was great.Now i get teh products in a slicer.Then i used the below

year month new = 'Test forlder'[M-Y].[Year]&'Test forlder'[M-Y].[MonthNo] to get the year and mont in YYYYMM value but then in teh slicer i dont get a between just a list and dropdown.can this be solved.

Will try out teh date solution as well but then 'Date = "1-" & table[Month] based on you format' what will this return if an example could be shown.when itried it gave 1-1 for january but then i guess there is more to it.

Thanks

[amitchandak]

If you make YYYYMM  datatype as a number then you can get between. But you may not be able to control value. Like it might allow 202013.

@Anonymous , Hope I got it correctly ?

[Anonymous]

Hello @amitchandak 

Yes when i convert it to whole numnber then i se the issue the slicer can get any number in between 20101(say january) to 202012(2020 dec) like 188477.Could you please explain on hte date solution then,maybe i need to get that  in.

Thanks

[Anonymous]

Hello @amitchandak ,

I guess it is solved.The Unpivot was the best part and then not getting it in the Axis was a small visualization thing i guess.I had hte date format in good one itself.I changed the X-aXIS Type to 'categorical' and it shows all the months and year.Thanks a lot.