Previous full year averages on a chart with current week averages

[brickhouse3539]

Hello, 

I have bar charts created for four main countries deplaying the last several weekly averages.  Each country has an air and an ocean chart.  What I need to do is add a line to each chart showing the full year 2019 averages (essentially a straight line).  The point here is to help identify when things may be taking longer than historically acheived.  Please help.  Thanks!

[v-lid-msft]

Hi @brickhouse3539 ,

We can try to use the following measure to meet your requriement:

2019 Avg =
CALCULATE (
    AVERAGE ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table'[Date] ),
        YEAR ( [Date] ) = 2019
    )
)

If it does not work, could you please share the formula of 2019 Avg used in your screenshot if it does not contain any confidential information?

Best regards,

[brickhouse3539]

I've tried all three suggestions offered as a solution so far and none of them have worked.  The results I'm getting are averaged differently week over week and also only showing in the 2019 week's data when I unfilter the 2020 weeks viewed.  What I want is the entire 2019 average by mode and destination country displayed as a straight line over the current weeks filtered (my initial graph in request for help was displaying everything after 1/31/2020.  A suggestion was offered about adding filters into a formula but whether I use allexcept, all, etc... all of these are confusing on how they actually work and I've tried so many combinations I can't get them to work.  The graphs are setup by country, shipping mode, with current weekly average days (all weeks after 1/31/2020 is displayed currently but that will change as the weeks go by since we only need the last few weeks. 

[brickhouse3539]

Can anyone help me resolve this issue please?  Need more info?  I thought I provided more than enough but if not, let me know.  Thanks!

[amitchandak]

@brickhouse3539 

Can you share expected output along with some sample source data explain how it gets achieved and mark all the solution provided @

In case you are looking for week comparison over year. Refer :https://www.dropbox.com/s/ef80ybxg2tzyjsy/sales_analytics_weekWiseWorks.pbix?dl=0

[brickhouse3539 · Accepted solution]

Hello all I figured it out thanks.  I had the formula right, I just didn't have the years on the chart and power bi must of been confused with the weeks from the two years in the same bar not knowing which previous year to show in the line.  That's why it appeared in the legend and nothing in the chart.

[Greg_Deckler]

Do you have any options in the Analytics pane?

You can always do something like AVERAGEX(ALL('Table'),[Value]) to get an average value over everything. (or use ALLEXCEPT or any FILTER criteria you wish. 

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

Use time intelligence with date table

Last YTD complete Sales = CALCULATE(AVERAGE(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos.