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