Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with chart and filters

Hello All,

 

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

 

Date AlPT AlPUSTAlAEURT
Jan-101.52.51.9
Feb-1022.82.5
...   
Dec-2097.95.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

 

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    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!

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.