cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sascha_17 Frequent Visitor
Frequent Visitor

PowerBI Chart with AVG of previous year

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 ?

 

AVG-PY%20Chart

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: PowerBI Chart with AVG of previous year

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:

Capture.PNG

 

Visual:

Capture2.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
7 REPLIES 7
Dave93 Frequent Visitor
Frequent Visitor

Re: PowerBI Chart with AVG of previous year

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 Frequent Visitor
Frequent Visitor

Re: PowerBI Chart with AVG of previous year

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 Frequent Visitor
Frequent Visitor

Re: PowerBI Chart with AVG of previous year

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 Frequent Visitor
Frequent Visitor

Re: PowerBI Chart with AVG of previous year

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 Frequent Visitor
Frequent Visitor

Re: PowerBI Chart with AVG of previous year

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 Frequent Visitor
Frequent Visitor

Re: PowerBI Chart with AVG of previous year

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...

Community Support Team
Community Support Team

Re: PowerBI Chart with AVG of previous year

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:

Capture.PNG

 

Visual:

Capture2.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |