PowerBI Chart with AVG of previous year

[Sascha_17]

Dear all,

I am trying to make following Chart in Power Bi - Please see picture below. How can I do that Chart with the AVG of the previous Year in the beginning of the new Year ? How do I have to calculate this in DAX ?

[Dave93]

Hi Sascha_17,

You could try something like this:

Average previous year =
CALCULATE (
    AVERAGE ( 'table'[column] );
    YEAR ( 'table'[date] )
        = YEAR ( TODAY () - 1 )
)

Here I'm calculating the average of all the records from pervious year. I'm doing this by getting the current year and substract one year off it. So in this example I'm getting the average of all the records in 2015. 

[Sascha_17]

Hi Dave93

Thanks for your answer - Your Calculation looks good. But how can I add this measure to my above chart now that it always appears before January ?

[Dave93]

A simple solution could be creating an additional column that stores the months and add them to the Axis. Or you could create a date table that stores all the dates between a period off time with the month names, quarters, etc and make a releationship between that table and your date.

[Sascha_17]

Would that working ? Because, in January I would like to have the data of the current year, not the AVG of the PY. So basically in the X Axis should be: AVG PY 15, Jan 16, Feb 16 etc...

You know what I mean ?

[Dave93]

No sorry I was in the understanding that you wanted just a line that was showing the average of previous year. But if I understand you now correctly, you want one line that start with the overall average off the previous year and flows into january of the current year, february and so on.

To do so you could create a column that stores the month names of the dates in the current year, but if it is a date in the previous year it should store "AVG Previous Year". That is something you can add to your Axis. Than you can just add the column you want the avg off to the values panel of the line chart and choose "Average".

To get the right sorting on the Axis you should make a 'sorting column' that gives "AVG Previous Year" a 0 value and Januari -> 1, February 2... and sort the 'months name column' by the 'sorting column'. 

Hopefully you understand what I'm explaning. Success

[Sascha_17]

Hi Dave

Thanks for your answer - I got your point. But how can I handle this case, when I have several years ? So in example when the user select 2013 then it should show the AVG from 2012 and when selected 2015 it should show the AVG 2014...

[v-shex-msft · Accepted solution]

Hi @Sascha_17,

You can take a look at below sample:

Measure:

Pre Year Monthly Average =
var temp=AVERAGEX(FILTER(SUMMARIZE(ALL(Cycle),Cycle[Date].[Year],Cycle[Date].[MonthNo],"Amount",SUM(Cycle[Amount])),[Date].[Year]=MAX(Cycle[Date].[Year])-1),[Amount])
return
if(MONTH(MAX([Date]))<=1,if(temp>0,temp,0),BLANK())

Table:

Visual:

Regards,

Xiaoxin Sheng