Display a constant line showing average of previous 7 weeks.

[Ani1991]

Hi Team,

Greetings of the day!!

I have a requirement where I am suppose to show a constant line in a 'line and stack column chart' visual where the line must be the average of revenue from the selected week upto 7 weeks before the selected week.
For example: If I select a week say 14 then the graph displays the revenue for all the dates for week number 14 and there should be a constant line which shows the average value of the revenue from 7th week to 14th week.
I used the below measure to achieve this but it didn't work.

AvgRevenueofPrior7Weeks =

var startdate = CALCULATE(MIN('Fact'[Create Date]),FILTER(ALLEXCEPT('Fact',DateDim[Week]),'Fact'[Week] = SELECTEDVALUE(DateDim[Week])-7))

var enddate = MAX('Fact'[Create Date])

var avgrevenue = CALCULATE(SUM('Fact'[ProductRevenue]),All('Fact'),'Fact'[Create Date] >= startdate && 'Fact'[Create Date] <= enddate)/DATEDIFF(startdate,enddate,DAY)

RETURN

avgrevenue

The graph that I am getting is something like below. The line is varying with respect to dates, but I need to avoid that. It must be a straight line showing the average of revenue from 7th week to 14th week.

I want the line to be a straight line showing just the average of revenue.

Any help would be highly appreciated.

Thanks,

Ani

[v-xuding-msft]

Hi @Ani1991 ,

Can you please post a dummy file that easier to help?  If you can't, please post some sample data and the expected result.

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

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ani1991]

Hi @v-xuding-msft ,

Please find the below data. I am sorry I am pasting it over here as I didn't have the option to attach the file. Also please find the screenshot of what I am currently getting. I want to show a straight line (instead of the line currently showing up in the screenshot) which is average of Revenue for last & weeks. 

The Date used is CreatedDate column from the below table and the revenue is coming from the Revenue column.

The measure I have written is as below:

AvgRevenueofPrior7Weeks_test =
var startdate = CALCULATE(MIN('Fact'[Create Date]),FILTER(ALLEXCEPT('Fact',DateDim[Week]),'Fact'[Week] = SELECTEDVALUE(DateDim[Week])-7))
var enddate = MAX('Fact'[Create Date])
var avgrevenue = CALCULATE(SUM('Fact'[ProductRevenue]),All('Fact'),'Fact'[Create Date] >= startdate && 'Fact'[Create Date] <= enddate)/DATEDIFF(startdate,enddate,DAY)
RETURN
avgrevenue

EstimatedCloseDate	Opportunity ID	Sales Stage ID	Account ID	Partner ID	Product ID	Revenue	FactoredProductRevenue	Create Date	Opportunity Days	Year	Month_Number	Month	Week
6/30/2015 10:59	39	1	22	22	17	$4,862,891	$486,289	6/7/2014 0:00	388	2015	6	Jun	23
6/30/2015 10:59	64	1	55	22	22	$5,688,641	$568,864	5/11/2014 0:00	415	2015	6	Jun	20
6/30/2015 10:59	126	1	104	22	10	$1,433,275	$143,328	1/2/2014 0:00	544	2015	6	Jun	1
6/30/2015 10:59	346	1	246	22	17	$3,501,919	$350,192	2/3/2014 0:00	512	2015	6	Jun	6
6/30/2015 10:59	347	1	269	22	16	$2,230,393	$223,039	4/28/2014 0:00	428	2015	6	Jun	18
6/30/2015 10:59	353	1	275	22	18	$535,605	$53,561	6/20/2014 0:00	375	2015	6	Jun	25
6/30/2015 10:59	373	1	291	22	8	$5,626,094	$562,609	6/19/2014 0:00	376	2015	6	Jun	25
6/30/2015 10:59	374	1	292	22	6	$595,469	$59,547	4/8/2014 0:00	448	2015	6	Jun	15
6/30/2015 10:59	393	1	306	22	4	$3,412,054	$341,205	6/8/2014 0:00	387	2015	6	Jun	24
6/30/2015 10:59	394	1	307	22	11	$5,981,812	$598,181	6/1/2014 0:00	394	2015	6	Jun	23
6/30/2015 10:59	404	1	170	22	11	$833,461	$83,346	3/23/2014 0:00	464	2015	6	Jun	13
6/30/2015 10:59	414	1	323	22	23	$5,299,231	$529,923	4/25/2014 0:00	431	2015	6	Jun	17
6/30/2015 10:59	418	1	11	22	17	$1,133,172	$113,317	5/16/2014 0:00	410	2015	6	Jun	20
6/29/2015 10:59	384	1	298	22	10	$2,386,868	$238,687	4/1/2014 0:00	454	2015	6	Jun	14
6/27/2015 10:59	355	1	277	22	12	$2,702,266	$270,227	3/25/2014 0:00	459	2015	6	Jun	13
6/26/2015 10:59	242	1	193	22	5	$2,154,078	$215,408	6/5/2014 0:00	386	2015	6	Jun	23
6/26/2015 10:59	370	1	22	22	16	$4,984,622	$498,462	6/21/2014 0:00	370	2015	6	Jun	25
6/26/2015 10:59	425	1	52	22	9	$1,959,131	$195,913	6/22/2014 0:00	369	2015	6	Jun	26
6/25/2015 10:59	228	1	180	22	13	$188,316	$18,832	1/16/2014 0:00	525	2015	6	Jun	3
6/24/2015 10:59	395	1	308	22	21	$4,022,403	$402,240	5/7/2014 0:00	413	2015	6	Jun	19
6/19/2015 10:59	326	1	252	22	13	$3,251,419	$325,142	3/16/2014 0:00	460	2015	6	Jun	12
6/19/2015 10:59	337	1	260	22	22	$6,168,120	$616,812	1/6/2014 0:00	529	2015	6	Jun	2
6/19/2015 10:59	417	1	326	22	13	$931,923	$93,192	3/3/2014 0:00	473	2015	6	Jun	10
6/15/2015 10:59	178	1	142	22	17	$6,407,018	$640,702	3/12/2014 0:00	460	2015	6	Jun	11
6/15/2015 10:59	182	1	146	22	19	$2,051,691	$205,169	2/8/2014 0:00	492	2015	6	Jun	6
6/15/2015 10:59	186	1	150	22	15	$1,174,333	$117,433	2/4/2014 0:00	496	2015	6	Jun	6
6/15/2015 10:59	187	1	151	22	9	$3,347,026	$334,703	2/5/2014 0:00	495	2015	6	Jun	6
6/12/2015 10:59	386	1	300	22	23	$1,853,211	$185,321	4/12/2014 0:00	426	2015	6	Jun	15
6/12/2015 10:59	390	1	303	22	25	$5,460,728	$546,073	6/1/2014 0:00	376	2015	6	Jun	23
6/8/2015 10:59	77	1	65	22	10	$5,294,496	$529,450	3/1/2014 0:00	464	2015	6	Jun	9
6/5/2015 10:59	192	1	155	22	18	$4,053,946	$405,395	5/4/2014 0:00	397	2015	6	Jun	19
6/1/2015 10:59	80	1	68	22	6	$2,332,519	$233,252	6/1/2014 0:00	365	2015	6	Jun	23

---

[Ani1991]

Hi Team,

Any update on the above query. Actually I tried a number of things but is unable to get it. Any help would be highly appreciated.

Thanks,

Ani

[Ani1991]

Hi Team,

Any help with regards to the above post?

Thanks,

Ani