topic Re: Get the average sales value based on last 7 days but if there are no sales there is no entry in DAX Commands and Tips

Get the average sales value based on last 7 days but if there are no sales there is no entry

Anonymous — Wed, 06 Mar 2024 19:37:30 GMT

Hello All,

I attempting to get the average sales for the last 7 days of a sales table like this:

Item	Date	QTY
AA	1/1	1
AA	1/2	2
AA	1/4	2
AA	1/6	2

The problem i'm running into is that If you Average() or Averagex() and use whichever date filter, you get the average of values that exist. Ideally, I would want the average including the 0 sales on 1/5,1/7 and 1/3 but those transaction don't appear in my table.

I would want a dax expression to result to this:

Item	7 Day Average
AA	1

Re: Get the average sales value based on last 7 days but if there are no sales there is no entry

Anonymous — Wed, 06 Mar 2024 19:39:30 GMT

I forgot to mention, I have a calendar table connected to this sales table

Re: Get the average sales value based on last 7 days but if there are no sales there is no entry

Anonymous — Wed, 06 Mar 2024 21:44:13 GMT

Found a solution!

7 Day Moving Average =

VAR SelectedDate = MAX(Calendar[Date])

VAR StartDate = SelectedDate - 6 VAR TotalSales = CALCULATE ( SUM ( Sales[Amount] ), DATESINPERIOD ( Calendar[Date], StartDate, SelectedDate, DAY ) )

VAR TotalDays = COUNTROWS ( DATESINPERIOD ( Calendar[Date], StartDate, SelectedDate, DAY ) )

RETURN DIVIDE ( TotalSales, TotalDays, 0 )