How to get the average sales of last 3 months

[krist_pbi]

Hey, I'm looking to get the average sales of last 3 months, I have been using these approaches but want to make sure the one who works better

f1: 

AVERAGEX( DATESINPERIOD( calendar[date], MAX(calendar[date]), -3, MONTH), fact_sales[total_sales])

The problem with the first one, is that for some reason is returning blank, I have marked my calendar as calendar table, the relationship is 1:* towards the fact sales but it doesn't work, this formula works only if I use the dates from the fact sales instead of the calendar dates.

f2: 

CALCULATE( AVERAGE(sales[total_sales]), calendar[date] >= MAX(fact_sales[Date]) - 90 ) ​

this is working but not sure if it's the correct approach 

Also would like to know if I can exclude the current month from the last 3 months, and also a formula that includes the current month just to make sure I have what I need

Thanks

[darkniqht · Accepted solution]

Using AVERAGEX: Your first formula should work, but if it’s returning blank, it might be due to context issues. Ensure your date column in the calendar table is properly related to the fact_sales. Here's a modified version:

AVERAGEX(
    DATESINPERIOD(calendar[date], MAX(calendar[date]), -3, MONTH),
    CALCULATE(SUM(fact_sales[total_sales]))
)

This ensures you're summing the sales correctly within the context of the selected dates.

Using CALCULATE with a Filter: Your second formula can be simplified, but it’s generally not recommended to use fixed days (like 90) since months vary in length. Instead, stick with DATESINPERIOD for accuracy.

To exclude the current month from your average, you can adjust your formula like this:

AVERAGEX(
    DATESINPERIOD(calendar[date], EOMONTH(MAX(calendar[date]), -1), -3, MONTH),
    CALCULATE(SUM(fact_sales[total_sales]))
)

To include the current month, you can simply adjust the DATESINPERIOD to cover the current month as well:

AVERAGEX(
    DATESINPERIOD(calendar[date], MAX(calendar[date]), -3, MONTH),
    CALCULATE(SUM(fact_sales[total_sales]))
)
)

[v-kongfanf-msft · Accepted solution]

Hi @krist_pbi ,

You can modify your formula like below:

result = 
CALCULATE (
    AVERAGE ( fact_sales[Total_Sales] ),
    calendar[Date] >= EOMONTH ( MAX ( fact_sales[Date] ), -3 ) + 1,
    calendar[Date] <= EOMONTH ( MAX ( fact_sales[Date] ), -1 )
)

Best Regards,
Adamk Kong

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

[darkniqht · Accepted solution]

Using AVERAGEX: Your first formula should work, but if it’s returning blank, it might be due to context issues. Ensure your date column in the calendar table is properly related to the fact_sales. Here's a modified version:

AVERAGEX(
    DATESINPERIOD(calendar[date], MAX(calendar[date]), -3, MONTH),
    CALCULATE(SUM(fact_sales[total_sales]))
)

This ensures you're summing the sales correctly within the context of the selected dates.

Using CALCULATE with a Filter: Your second formula can be simplified, but it’s generally not recommended to use fixed days (like 90) since months vary in length. Instead, stick with DATESINPERIOD for accuracy.

To exclude the current month from your average, you can adjust your formula like this:

AVERAGEX(
    DATESINPERIOD(calendar[date], EOMONTH(MAX(calendar[date]), -1), -3, MONTH),
    CALCULATE(SUM(fact_sales[total_sales]))
)

To include the current month, you can simply adjust the DATESINPERIOD to cover the current month as well:

AVERAGEX(
    DATESINPERIOD(calendar[date], MAX(calendar[date]), -3, MONTH),
    CALCULATE(SUM(fact_sales[total_sales]))
)
)

[rajendraongole1]

Hi @krist_pbi -If you want the average sales over the last 3 full months

use below measure:

AverageSalesLast3Months :=
AVERAGEX(
DATESBETWEEN(
calendar[date],
EOMONTH(MAX(calendar[date]), -2) + 1,
MAX(calendar[date])
),
fact_sales[total_sales]
)

excluding current month:

AverageSalesLast3MonthsExcludingCurrent :=
AVERAGEX(
DATESBETWEEN(
calendar[date],
EOMONTH(MAX(calendar[date]), -3) + 1,
EOMONTH(MAX(calendar[date]), -1)
),
fact_sales[total_sales]
)

Use DATESBETWEEN to define a precise date range if DATESINPERIOD gives blanks.

Hope this works.