cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smireles
Frequent Visitor

Average of Prior Weeks based on Location

smireles_0-1597182511320.png

I am trying to calculate the average of the 4 previous weeks based on the location.

 

Then I want to take that average and get the difference for the current week.

 

the % of increase is the Difference of Avg/Avg of prior weeks. This is the end goal but I am currently stuck trying to tget the averages for the different llocations.

 

Any direction with this would be helpful!

 

Thanks!

4 REPLIES 4
Eyelyn9
Community Support
Community Support

Hi @smireles ,

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

Eyelyn9
Community Support
Community Support

Hi @smireles ,

According to my understanding, you want to calculate the average of prior weeks ,the difference and the % of increase based on current week , right?

 

You could use the following formula:

 

avg of priod weeks =
(
    SELECTEDVALUE ( 'Priod'[07/03/2020] ) + SELECTEDVALUE ( 'Priod'[07/10/2020] )
        + SELECTEDVALUE ( 'Priod'[07/17/2020] )
        + SELECTEDVALUE ( 'Priod'[07/24/2020] )
) / 4
Difference of Avg & current week =
SELECTEDVALUE ( 'Priod'[07/31/2020] ) - [avg of priod weeks]
% increase =
SELECTEDVALUE ( 'Priod'[07/31/2020] ) / [avg of priod weeks] – 1
Column =
[Difference of Avg & current week] / [avg of priod weeks]

 

My visualization looks like this:

8.12.8.PNG

Is the result what you want?Hope to reply to me.Thanks~

 

Best Regards,

Eyelyn Qin

andre
Memorable Member
Memorable Member

here is a tutorial that walks you through all the steps to calculate the average of prior weeks and also gives you an option for selecting how many weeks to compare

if you would like to practice, you can download the tutorial materials here: https://businessintelligist.com/2020/08/11/last-n-weeks-comparisons-dax-tutorial-last-4-weeks-last-8...

lbendlin
Super User III
Super User III

Define "prior"  - are these the previous four weeks, or the previous four entries which may have week gaps between them?

 

In the first case you can do simple date math (day - 28, for example), but in the second case you are looking at TOPN(4) with the appropriate date filter.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors