cancel
Showing results for
Did you mean:
Highlighted
Member

## Display a constant line showing average of previous 7 weeks.

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
4 REPLIES 4
Member

## Re: Display a constant line showing average of previous 7 weeks.

Hi Team,

Any help with regards to the above post?

Thanks,

Ani

Community Support Team

## Re: Display a constant line showing average of previous 7 weeks.

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.

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.

Member

## Re: Display a constant line showing average of previous 7 weeks.

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

Member

## Re: Display a constant line showing average of previous 7 weeks.

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

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 365 members 4,050 guests
Recent signins: