Dynamic Multiple Line Chart based on slicer

[Zennificator]

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 creation of a dynamic multiple line chart based on the values of a slicer. 

In details

I need to create a line chart that shows below lines based on the selected year 

- Selected Year Actuals

- Selected Year Budget

- Selected Year -1 Actuals

A few constraints I have:

- The Year Column is a whole number (2019, 2018, 2017, etc)

- I have a month column in which months are expressed as text (January, February, March,...)

- I have one column with Actual Values

- I have one column wiht Budget Values

- I have a column for Data Type (Budget, Actual)

I've tried multiple solution using different date functions. They don't work properly as I don't have a proper date column. But probably I'm missing somehting in my newbietude

In Tableau this was solved this way (ndr I know they are two very different tools but it's to get the idea):

1) I've created a year parameter with different year values to be used as slicer (matching the year column values)

2) Create one Measure adding up Actual and Budget

3) Create two select measures. One selects the sum of Actual and Budget where year = parameter selection. The second selects only Actual values where year = parameter -1.

4) drop the two measures into a line chart

5) use year and data type as legend (eg, if 2019 is selected we will have 2019 Actuals, 2019 Budget, 2018 Actuals)

I've used the search function to look for a similar problem but I didn't find any. But, if I've overlooked something similar I will be happy to close this down

Thanks in advance for any help!

Zen

Edit: Link to sample table:

https://docs.google.com/spreadsheets/d/1j-obrgTNnDWWcAXY0khzblO11-7whCED22P0h1E3llk/edit?usp=sharing

[AndrejZitnay]

Hello @Zennificator 

I would recommend to you to spend bit of time on guided learning.

https://docs.microsoft.com/en-us/power-bi/guided-learning/

It helped me a lot at beginning of my journey with Power BI.

(It is really beneficiary to go step by step)

Everything what you want is possible but difficult without seeing your data.

Andrej

[Zennificator]

Hi Andrej

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

Zen

[AndrejZitnay]

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]

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]

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

https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks

[Zennificator · Accepted solution]

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