Last 7 days average revenue

[JamesBowdidge]

Hi,

This must be simple but its beaten me.. All I need is a measure to calulate just the last 7 days average revenue..

Table is below, each line is a sale on the date specified.

All I want is the average daily total for the last 7 days.. so totaling all sales from yesterday, back 7 days and providing the average..

thanks

[JamesBowdidge]

@Greg_Deckler 

[Greg_Deckler]

@JamesBowdidge Try:

Measure = 
  VAR __Date = MAX('Table'[Booked Date])
  VAR __MaxDate = __Date - 1
  VAR __MinDate = __MaxDate - 7
RETURN
  AVERAGEX(FILTER(ALL('Table'),[Booked Date] >= __MinDate && [Booked Date] <= __MaxDate),[LRev])

[JamesBowdidge]

Hi Mate, contrary to saying its the solution (hit the wrong button)

Your formular seems to be averaging the last 7 records (and returns 62.90, rather then the expected 170,000 ish)..

I need the sum of each of the 7 days sales, and then average those numbers.. 

thanks for your help!

[Greg_Deckler · Accepted solution]

@JamesBowdidge OK, how about:

Measure = 
  VAR __Date = MAX('Table'[Booked Date])
  VAR __MaxDate = __Date - 1
  VAR __MinDate = __MaxDate - 7
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Booked Date] >= __MinDate && [Booked Date] <= __MaxDate),[Booked Date],"__LRev",SUM('Table'[LRev]))
RETURN
  AVERAGEX(__Table,[__LRev])

[JamesBowdidge]

@Greg_Deckler 

perfect, that got it! accepted as solution.. thanks so much!

DAX is very new to me.. hopeing it wont take long to pick it up!! 

thanks once again for your help!