cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ani1991 Member
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.
image.pngI 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
Ani1991 Member
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
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.

 

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

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

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
 


image.png

 

EstimatedCloseDateOpportunity IDSales Stage IDAccount IDPartner IDProduct IDRevenueFactoredProductRevenueCreate DateOpportunity DaysYearMonth_NumberMonthWeek
6/30/2015 10:59391222217$4,862,891$486,2896/7/2014 0:0038820156Jun23
6/30/2015 10:59641552222$5,688,641$568,8645/11/2014 0:0041520156Jun20
6/30/2015 10:5912611042210$1,433,275$143,3281/2/2014 0:0054420156Jun1
6/30/2015 10:5934612462217$3,501,919$350,1922/3/2014 0:0051220156Jun6
6/30/2015 10:5934712692216$2,230,393$223,0394/28/2014 0:0042820156Jun18
6/30/2015 10:5935312752218$535,605$53,5616/20/2014 0:0037520156Jun25
6/30/2015 10:593731291228$5,626,094$562,6096/19/2014 0:0037620156Jun25
6/30/2015 10:593741292226$595,469$59,5474/8/2014 0:0044820156Jun15
6/30/2015 10:593931306224$3,412,054$341,2056/8/2014 0:0038720156Jun24
6/30/2015 10:5939413072211$5,981,812$598,1816/1/2014 0:0039420156Jun23
6/30/2015 10:5940411702211$833,461$83,3463/23/2014 0:0046420156Jun13
6/30/2015 10:5941413232223$5,299,231$529,9234/25/2014 0:0043120156Jun17
6/30/2015 10:594181112217$1,133,172$113,3175/16/2014 0:0041020156Jun20
6/29/2015 10:5938412982210$2,386,868$238,6874/1/2014 0:0045420156Jun14
6/27/2015 10:5935512772212$2,702,266$270,2273/25/2014 0:0045920156Jun13
6/26/2015 10:592421193225$2,154,078$215,4086/5/2014 0:0038620156Jun23
6/26/2015 10:593701222216$4,984,622$498,4626/21/2014 0:0037020156Jun25
6/26/2015 10:59425152229$1,959,131$195,9136/22/2014 0:0036920156Jun26
6/25/2015 10:5922811802213$188,316$18,8321/16/2014 0:0052520156Jun3
6/24/2015 10:5939513082221$4,022,403$402,2405/7/2014 0:0041320156Jun19
6/19/2015 10:5932612522213$3,251,419$325,1423/16/2014 0:0046020156Jun12
6/19/2015 10:5933712602222$6,168,120$616,8121/6/2014 0:0052920156Jun2
6/19/2015 10:5941713262213$931,923$93,1923/3/2014 0:0047320156Jun10
6/15/2015 10:5917811422217$6,407,018$640,7023/12/2014 0:0046020156Jun11
6/15/2015 10:5918211462219$2,051,691$205,1692/8/2014 0:0049220156Jun6
6/15/2015 10:5918611502215$1,174,333$117,4332/4/2014 0:0049620156Jun6
6/15/2015 10:591871151229$3,347,026$334,7032/5/2014 0:0049520156Jun6
6/12/2015 10:5938613002223$1,853,211$185,3214/12/2014 0:0042620156Jun15
6/12/2015 10:5939013032225$5,460,728$546,0736/1/2014 0:0037620156Jun23
6/8/2015 10:59771652210$5,294,496$529,4503/1/2014 0:0046420156Jun9
6/5/2015 10:5919211552218$4,053,946$405,3955/4/2014 0:0039720156Jun19
6/1/2015 10:5980168226$2,332,519$233,2526/1/2014 0:0036520156Jun23

Ani1991 Member
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

Helpful resources

Announcements
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 365 members 4,050 guests
Please welcome our newest community members: