Forum Discussion

BabyBinki821's avatar
BabyBinki821
Helper I
4 years ago
Solved

Trailing 4 Week Average

Hi

 

So I thought I had this solved but i don't 😞 I need 4 week trailing average of the number of orders. Below you can see the graph I am trying to create . The formula below looks like it works but its giving me the current week - 3 previous weeks average. If I change the -3 to -4 I get very different numbers as well.

 

Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-3 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])))
 

 

  • amitchandak's avatar
    amitchandak
    4 years ago

    BabyBinki821 , If you do not want this week

     

    Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-4 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])-1  ))

     

    or

     

    Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-5 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])-1  ))

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    BabyBinki821 , If you do not want this week

     

    Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-4 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])-1  ))

     

    or

     

    Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-5 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])-1  ))

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.