Forum Discussion

Zennificator's avatar
Zennificator
Frequent Visitor
6 years ago
Solved

Dynamic Multiple Line Chart based on slicer

Hi All,   I'm gently shifting from Tableau to PowerBi and hence I'm in that painful moment in which I'm recreating all the reports on this awesome new tool.  One thing in which I'm stuck is the cr...
Need Help
Tips and Tricks
  • Zennificator's avatar
    Zennificator
    6 years ago

    Hi AndrejZitnay ,

     

    Thanks for your reply,

     

    Unfortunately this method is not sustainable in the longrun (creating a new graph each time a new year starts)

     

    I've just found out an easier way to do so which is fully automated and does require very little maintenance:

     

    Step 1 - Create a new table (name Table) with year as text and their equivalent as first of January (eg, 2019 = 01/01/2019, 2018 = 01/01/2018)

    Step 2 - Link the two table using the text year columns

    Step 3 - Create a measure just for Previous Year = CALCULATE(([Actuals]),PREVIOUSYEAR('Table'[Date]))

    Step 4 - Drop the three measures in one graph

    Step 5 - Create a slicer using the Dates from the new "Table" and set the formats as yyyy

     

    Only maintenace to do is to add new year in the table as both text and date. I've already added until 2028. If the user selects a date with no data it will blank the graph forcing her/him to select another date.

     

    Even if I've found a solution myself thanks for providing some answer that managed to order my thoughts

     

    Best,

    Davide

Zennificator's avatar
Zennificator
Frequent Visitor
6 years ago

Hi Andrej

 

Thanks for your reply, I've added a link to a sample table

 

Zen

  • AndrejZitnay's avatar
    AndrejZitnay
    Post Patron
    6 years ago

    Hello Zennificator 

     

    1    I recommend to Merge Values into one column and change it to Values. 

          You can do that in edit queries

     

    2     Create basic table which will help to give order to Months

     

    3     Create relationship between tables

     

    4     Sort your table by Order

     

    5     Create you chart (Month from Table in Axis)

                                       Type in Legend

                                        Values in Value

     

        Year should be in separate slicer

     

    That's how I work with my actuals and budgets.

     

     

     

    • Zennificator's avatar
      Zennificator
      Frequent Visitor
      6 years ago

      Hi Andrej,

       

      Thank you for your reply.

       

      However this does not answer my question

       

      My graph (preferrable a line chart) has to show three measures:

       

      - Year N actuals

      - Year N budget

      - Year N-1 actuals

       

      Where N is the value (year) selected on the slicer

       

      Is there a way to achieve this working with with year as a value and not as a date (I know there is a way to achieve this with SAMEPERIODLASTYEAR function but it needs dates value which i don't have)

       

      Best,

       

      Zen

       

      • AndrejZitnay's avatar
        AndrejZitnay
        Post Patron
        6 years ago

        Hello Zennificator 

         

        I would this measures and switch to line chart.

        I had to adjust 2018 actual figures as they were same as 2019 budget so lines were merged.

         

        2018 Actual = CALCULATE(SUM(Sheet1[Value]),FILTER(Sheet1,Sheet1[Year]=2018),FILTER(Sheet1,Sheet1[Type]="Actual"))
        2019 Actual = CALCULATE(SUM(Sheet1[Value]),FILTER(Sheet1,Sheet1[Year]=2019),FILTER(Sheet1,Sheet1[Type]="Actual"))
        2019 Budget = CALCULATE(SUM(Sheet1[Value]),FILTER(Sheet1,Sheet1[Year]=2019),FILTER(Sheet1,Sheet1[Type]="Budget"))
         
         
        This will give you line chart for 2019 vs 2018
        If you want to do 2018 vs 2017 I would do 2nd chart and use bookmarks instead of slicer