Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Trailing Week Average

Hi everyone, 

 

I have just started using Power BI and DAX however I have been struggling with a trailing average measure. My data is sorted like the example data below.

 

The problem I am having is that all functions seem to aim at dates instead of week numbers.

What formula could create a trailing average for 12 weeks excluding the current week? 

 

Kind regards, 

Majesteit 

Week NoSalesvalueParameter 1 Parameter 2 

1

345PromoStore
134RegularOnline
221 PromoStore
21245RegularOnline
35636PromoStore
337RegularOnline
435PromoStore
Need Help
Show and Tell

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous You will need to do something like:

    Measure = 
  VAR __CurrentWeek = WEEKNUM(TODAY())
  VAR __StartWeek = __CurrentWeek - 13
RETURN
  AVERAGEX(FILTER('Table', [Week No] >= __StartWeek && [Week No] < __CurrentWeek),[Salesvalue])
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Dear Greg, 

     

    Thank you for the response, it is working for the individual sales values however I need the sum of the entire week and then the average for the sum of the different weeks. 

     

    Kind regards