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

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
 
 
Zennificator's avatar
Zennificator
Frequent Visitor
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