average sales quantity last 3 months

[Sofinobi]

hello community,

i have a meseare to calculate the sales quantity,

i need to calculate the average sales of last 3 months
eg; we are on january, i need the sum of last 3 months /3 (october, november and december)

and next months, it calculates (november, december and january)

i copied a measure from SQLBI, but it doesn't works as i want

Sales R3M =

VAR NumOfMonths = 3

VAR LastCurrentDate =

    MAX ( 'Date'[Date] )

VAR Period =

    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

    CALCULATE (

        SUMX(

            VALUES ( 'Date'[Month Year] ),

            [Sales Quantity]

        )/3,

        Period

    )

VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )

VAR LastDateWithSales = MAX ( COM_DocumentDetail[CreationDate] )

RETURN

    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

thank you all

[v-zhangti]

Hi, @Sofinobi 

You can try the following methods.
Measure:

Month Quantity = CALCULATE(SUM('Table'[Ouantity Now]),ALLEXCEPT('Date','Date'[Month]))

Cumulative 3 month = 
Var N1=SUMMARIZE(FILTER(ALL('Date'),[Month]<=SELECTEDVALUE('Date'[Month])),[Month],"Sum",[Month Quantity])
Var N2=TOPN(3,N1,[Month],DESC)
Return
SUMX(N2,[Sum])

Always calculate the sum of the last 3 months. Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Sofinobi]

thank you for your support, the answer from Johnt75 is exactely what i'm looking for.

thanks for you again

[johnt75 · Accepted solution]

That version is including the current month in the calculation, you want to exclude that so I think you just need to tweak the LastCurrentDate

Sales R3M =
VAR NumOfMonths = 3
VAR LastCurrentDate =
    MIN ( 'Date'[Date] ) - 1
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        SUMX ( VALUES ( 'Date'[Month Year] ), [Sales Quantity] ) / 3,
        Period
    )
VAR FirstDateInPeriod =
    MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales =
    MAX ( COM_DocumentDetail[CreationDate] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

[Sofinobi]

thank you @johnt75 , thats perfect