adding trendline for each time- interval of the x-axis

[vishnuprashanth]

Hello Everyone,

I need to add trendlines to my line chart for every intervals of the X-Axis(date). Is there any way to achieve this in power bi?


Actual OP:
line chart with single straight trendline for all date of X-Axis

Expected OP:
Trendline changing for each half year interval of X-Axis

 or:

Trendline or Moving Avg line for each Q_Year.

I can add a trendline for the entire data, but cannot add for each time-interval I wanted. Any suggestion to achieve this, please?

TIA 

[quentin_vigne · Accepted solution]

@vishnuprashanth

Sorry, i've mislead you.

There is a MUCH simpler solution.

Create a quick measure -> Rolling Average -> 

And just fill it like I did on the screenshot, It should work (It's in French but it will look exactly the same in your language) : 

[quentin_vigne]

Here what I have with the values you gave me : @vishnuprashanth

If it stop in august it's because I don't have value for june

[vishnuprashanth]

Thanks @quentin_vigne. 

But I am afraid that this doesn't actually help me. It takes into account of the month alone in the x-axis. 

But I want it for the interval of every 6 months rather than only for the individual months. July to Dec 2016, Jan to June 2017, July - Dec 2017 and Jan - June 2018. 

Something like the below:
trendline or moving average like for each Q_year instead of individual months

[vishnuprashanth · Accepted solution]

Managed to achieve it by adding the Q_Year to the legend and unchecking combine trendline in the Analytics pane. 

Okay, but it looks clumsy now  Need to find a solution to make it look better.add Q_Year in Legend and Unchecked combine Trend line in Analytics pane .

[quentin_vigne]

Hi @vishnuprashanth

I think what you need here is not a trend line but moving average line

There is some usefull tutorial on the community :

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

https://community.powerbi.com/t5/Desktop/Moving-Average/td-p/43041

Hope it will help you.

If you don't understand, I can try to explain it better but I will need sample of your date (table name / column names and datas)

Quentin

[vishnuprashanth]

Hi Quentin,

Thanks for your suggestions. I have seen the moving average solution before, but I really did not understand it.

But will go through the post again and try to get it. 

Here is the sample of my data attached. All these values are in the same table. 
sample data(all the variables are from the same table)

[quentin_vigne]

@vishnuprashanth

I need you to put your data in a table (like if you copy an excel table and paste it here) so that I can copy and paste them on my PowerBi file and do the job

Here is a usefull guide on How to get a question answered :

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[vishnuprashanth]

@quentin_vigne I tried to calculate moving energy by writing the query by combining the Month from my master calendar table.

Moving Avg Energy = 
CALCULATE (
Average(final_op2[Predicted Energy]),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[MonthNum] >= MAX ('Calendar'[MonthNum]) -6
&& 'Calendar'[MonthNum] <= MAX ( 'Calendar'[MonthNum] )))

But, I think there is something I need to correct in my query for getting the expected output. 
 With this query, I am getting the below-attached output. 

Moving average is in-line with the actual values rather than a trendline.

[quentin_vigne · Accepted solution]

@vishnuprashanth

Sorry, i've mislead you.

There is a MUCH simpler solution.

Create a quick measure -> Rolling Average -> 

And just fill it like I did on the screenshot, It should work (It's in French but it will look exactly the same in your language) : 

[vishnuprashanth]

This doesn't work @quentin_vigne.
Rolling avg measure doesnt show up the trend line in the chart

[vishnuprashanth]

date	Q_Year	Predicted_Energy	Max_energy
28/08/16	2016-H2	44621	44219
04/09/16	2016-H2	46205	47001
14/09/16	2016-H2	48219	49639
27/09/16	2016-H2	48117	51272
15/10/16	2016-H2	48202	46814
18/10/16	2016-H2	52548	57599
27/11/16	2016-H2	50232	51671
03/12/16	2016-H2	45465	46438
07/12/16	2016-H2	52565	56101
08/01/17	2017-H1	47349	37788
14/01/17	2017-H1	42425	38613
22/01/17	2017-H1	46827	47803
29/01/17	2017-H1	48264	50216
08/02/17	2017-H1	50822	54571
12/02/17	2017-H1	46370	25016
19/02/17	2017-H1	48342	50723
22/02/17	2017-H1	51309	43521
04/03/17	2017-H1	48874	50131
08/04/17	2017-H1	49998	43470
07/05/17	2017-H1	50303	25512
21/05/17	2017-H1	45838	47473
26/07/17	2017-H2	51489	37264
02/08/17	2017-H2	45169	38137
05/08/17	2017-H2	45829	33708
08/08/17	2017-H2	45236	37625
12/08/17	2017-H2	46370	48066
19/08/17	2017-H2	44999	43058
22/08/17	2017-H2	49654	35556
02/09/17	2017-H2	48413	49573
09/09/17	2017-H2	48856	51095
16/09/17	2017-H2	46888	45531
30/09/17	2017-H2	46297	44940
14/10/17	2017-H2	45296	13463
28/10/17	2017-H2	42476	35565
04/11/17	2017-H2	44672	44188
10/11/17	2017-H2	48853	34837
18/11/17	2017-H2	46749	47012
22/11/17	2017-H2	46315	27883
25/11/17	2017-H2	46871	46921
02/12/17	2017-H2	49893	49080
09/12/17	2017-H2	51421	51796
16/12/17	2017-H2	46124	48081
13/01/18	2018-H1	48755	50632
25/01/18	2018-H1	45597	37263
03/02/18	2018-H1	46401	46150
10/02/18	2018-H1	48085	48340
14/02/18	2018-H1	47816	46533
17/02/18	2018-H1	44866	42935
17/03/18	2018-H1	41654	38809
24/03/18	2018-H1	45137	43000
06/04/18	2018-H1	47874	47718
11/04/18	2018-H1	48824	48321
14/04/18	2018-H1	47391	47419
21/04/18	2018-H1	45999	45272
27/04/18	2018-H1	38981	5142

@quentin_vigne Apologies. I have now pasted the sample data above.