To calculate average of a product base on Day to days sales to determine product increase in sales

[shahidalaila23]

Hi, 

I have a table that have Day to day sales for one month. (ex: 1st July until 21st July *the current date) and the data will be updated until end of month. 

I need to analyze the product trend by calculating the average sales and percentage and see if the product have increase or decreasing in trend for the past 21 days. For now I have a separate fact table (Data Table[Sales]) and calendar table. 

This is the example product visual that I currently display on my report. 

please help, I have problem with trying to do this in DAX 

[v-alq-msft]

Hi, @shahidalaila23 

You may try the following measure to see if it helps.

x =
CALCULATE (
    AVERAGE ( table[Sales] ),
    DATESINPERIOD ( Calendar[Date], MAX ( Calendar[Date] ), -21, DAY )
)

If i misunderstood your thoughts, please show us some sampe data and expected results with OneDrive for business. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shahidalaila23]

After I go through the process again, I realise that I actualy need to calculate daily sales divide with number of days to get the average sales of the days, 

for ex, I have data of April until 12/04/2021 (*current date) and I filtered date to calculate from 1 to 7 of April. For now I have tried to calculate but it return value 0  

[amitchandak]

@shahidalaila23 , Past 21 Days Vs what ?

You can try a formula like these for 21 days of Jul vs 21 days of June

MTD QTY forced=
var _max = maxx(allselected(Date), Date[Date])
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALMTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LMTD QTY forced=
var _max1 = maxx(allselected(Date), Date[Date])
var _max = date(year(_max1), Month(_max1)-1, day(_max1))
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date]),-1,month)), blank())
//or

[shahidalaila23]

It was suppose to be calculation average daily sales, example the total of first week of the month divide with 7