How do I calculate the average sum of sales per month for a rolling 3?

[Libbyb23]

Hi,  

I am struggling with getting the correct output for a few measures. I am trying to calculate the total sum of sale invoiced and ordered in the same month and year per working day. 
These outputs are too high and should be around 150-165k. 

The first measure that is totaling 233.8k is:

Last 3 Daily = DIVIDE(([3 Month Trend] * 3), [Rolling 3m Working Days])

The 3 month trend measure is calculated by:

CALCULATE(SUM('Sales Data'[sales_price]), FILTER('Sales Data', 'Sales Data'[Order Date Trend] = 'Sales Data'[Invoice Date Trend]),

DATESINPERIOD('Date Dimension'[FullDateAlternateKey], [Today], -3, MONTH))

The rolling 3 month working day trend is calculated by:

Rolling 3m Working Days =

CALCULATE([Working Days Total], DATESINPERIOD('Date Dimension'[FullDateAlternateKey], EOMONTH([Today],-1), -3, MONTH))

*I dont think is calculating the average total sales per month correctly*

The working days total measure is calculated by:

Working Days Total = COUNTROWS(FILTER('Date Dimension', 'Date Dimension'[IsWorkingDay] = TRUE && 'Date Dimension'[IsHoliday] = FALSE))

Also, as I refresh the data everyday those numbers seem to jump by 30k, which isnt right. I am not sure what I am doing but any help is appreciated!

[Libbyb23]

Okay thanks, 

How would I get the average sales of the previous month + the average sales of 2 months ago + average sales of 3 months ago? I need it to be rolling. The highlighted part filters for sales that were ordered and invoiced in the same month since we do have sales that are ordered and invoiced in different months as well but I do not want those included in the sum.

[DataNinja777]

Hi @Libbyb23 ,

Looking at your formula, I am wondering why you needed to multiply by 3 as yellow highlighted the {3 Month Trend] measure which already seems to take account of the 3 months sales data.  It appears that multiplication by 3 should be removed to obtain a more reasonable output in line with your expectations.  

Best regards,

[Libbyb23]

Hi!

Thanks for the feedback. Do you know if that 3 month trend value is an average per month out of the last 3 months? Or is it a sum of the value of sales for 3 months?

[DataNinja777]

Hi @Libbyb23 ,

I am not sure about what exactly the yellow highlighted part represents.  However, the formula below represents 3 months sales up to today.  

Best regards,

[Libbyb23]

Hi, 

Hmm, could you help me calculate the sum of sales for previous month for sales that were ordered and placed in last month, then 2 months ago, etc?

1 month daily average = CALCULATE(

    SUM('Sales Data'[sales_price]), FILTER('Sales Data', 'Sales Data'[Order Date Trend] =  'Sales Data'[Invoice Date Trend]), DATEADD('Date Dimension'[FullDateAlternateKey],-1,MONTH))