Average per Previous Year Line

[AColeman]

Hi

I'm brand new to PowerBI and I'm teaching myself as i go along, and i must say this forum has been very helpfull to find previous topics i needed to solve some issues i had or needed to teach myself how to do it.

I however coudnt find a solution or similiar topic what my request is, and would love any help.

I have the folowing graph, My overtime and Budget figure are both from a Calculated measure as per below to provide me the last 12 months data.

Now i would like to add a line to this graph to display the overtime money average, I have a table where i have calculated the average per department per year but dont know if that will help

So what i would like to obtain is for my year 2016 i want to display the average for that department for 2015 and for 2017 i would like to display the average of 2017...if that makes sense...

So the red line will indicate what i require.

[v-caliao-msft]

Hi @AColeman,

You could have a calculated column to show the previous year average for the corresponding department.

PreviousAverage=Lookupvalue(Table[Average],Table[DerpartmentID],Table[DerpartmentID],Table[OvertimeYear],Table[OvertimeYear]-1）

Then you can use it on your visual.

If this is not what you want, please put your sample data into excel file and upload it, so that we can make further analysis.

Regards,

Charlie Liao

[AColeman]

Hi

Thanks for the response @v-caliao-msft

But i think im doing something wrong...

[v-caliao-msft]

Hi @AColeman,

Do you copy my sample DAX expression and modify the table name and file name?  Sorry for that in my DAX expression the closing parenthesis is incorrect, please delete it and re-type then you can get the expected result.

Regards,

Charlie Liao

[AColeman]

Nobody have any ideas how to achieve this result?