topic Re: average sales quantity last 3 months in DAX Commands and Tips

average sales quantity last 3 months

Sofinobi — Tue, 17 Jan 2023 15:57:38 GMT

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

Re: average sales quantity last 3 months

johnt75 — Tue, 17 Jan 2023 16:14:14 GMT

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 )

Re: average sales quantity last 3 months

v-zhangti — Wed, 18 Jan 2023 07:58:50 GMT

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.

Re: average sales quantity last 3 months

Sofinobi — Wed, 18 Jan 2023 08:30:22 GMT

thank you johnt75 , thats perfect

Re: average sales quantity last 3 months

Sofinobi — Wed, 18 Jan 2023 08:34:13 GMT

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

thanks for you again