cancel
Showing results for
Did you mean:
Helper II

## Average last 3 Months with Distinct Value

Hi Friends,

I need to calculate the average last 3-Month distinct store ordered our product.

Our product just released in March.

I tried the calculation using DATESINPERIOD Or DATEBETWEEN but it does not give me the expected result.

What I expect a result of average last 3-Month measure:

In June: ( 2259 + 1879 +1493 ) / 3 = 1877

In May: ( 1879 +1493) /2 = 1686 ( because only 2 months: April and Mar)

In Apr: 1493 ( Only Mar )

In Mar: Blank

Any help is highly appreciated.

Have a nice day, guys!

1 ACCEPTED SOLUTION
Community Support

Hi @tracytran91,

Try measure as:

``````Measure =
var curr_month=SELECTEDVALUE('Table'[Month])
var temp=
AVERAGEX(
FILTER(
ALLSELECTED('Table'[Month]),
'Table'[Month]<curr_month&&'Table'[Month]>=curr_month-3
),
DISTINCTCOUNT('Table'[Store Code]))
return temp``````

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

12 REPLIES 12
Community Support

You can create a column as:

``Month = MONTH('Table'[Invoice Date ])``

Create a measure as:

``````Measure =
CALCULATE(
SUM('Table'[Sales])/3,
FILTER(
ALL('Table'),
'Table'[Month]>=MAX('Table'[Month])-3 && 'Table'[Month]<=MAX('Table'[Month])-1
)
)``````

Here is the output:

The pbix is attached.

Best Regards,

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

Helper II

Hi @v-xulin-mstf , Please kindly have a look desired result.

Sample data here:

I would like to know:

At Channel DOMESTIC with stores with sales bigger than 0 ( It means excluding return),  What is the average last 3 Months of DISTINCT STORE ordered our product?

Community Support

Hi @tracytran91,

Try measure as:

``````Measure =
var curr_month=SELECTEDVALUE('Table'[Month])
var temp=
AVERAGEX(
FILTER(
ALLSELECTED('Table'[Month]),
'Table'[Month]<curr_month&&'Table'[Month]>=curr_month-3
),
DISTINCTCOUNT('Table'[Store Code]))
return temp``````

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

Super User III

Hi,

Try these measures

Measure  = averagex(summarize(filter(calendar,datesbetween(calendar[date],edate(min(calendar[date]),-3),min(calendar[date])-1)),Calendar[year],calendar(month name),"ABCD",[No. of distinct stores]),[abcd])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Hi @Ashish_Mathur , Could you please explain what ""ABCD" and [abcd] in your measure?  So I can replace them with appropriate column/meaure.

Super User III

Nothing.  Just left ABCD stay there - it is just the column title of the second column of the virtual table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

There is the error for this measure. 😞

Super User III

Hi,

Try this measure

=averagex(summarize(filter(values(calendar[month name]),datesbetween(calendar[date],edate(min(calendar[date]),-3),min(calendar[date])-1)),calendar(month name),"ABCD",[No. of distinct stores]),[abcd])

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

I tried once agin but it still does not work out. I created a sample excel file.

I would like to know:

At Channel DOMESTIC with stores with sales bigger than 0 ( It means excluding return),  What is the average last 3 Months of distinct store ordered our product?

Please kindly note that I have the Date table which has relationship with Sale table.

Your assistance regarding this matter is highly appreciated.

Helper II

I created 2 measures test3 and Rolling3 to follow your idea. However, they gave me same result as I did before.

Super User IV

You can replace distinctcount of the customer with

averageX(values(D_date[Month]), calculate(distinctcount('F Secondary Sale[customer])))

or try like this example

Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), filter(Sales,not(isblank(sum(Sales[Sales]))))))

Proud to be a Super User!

Super User III

Hi,

Share your PBI file with the distinct store measure already written there.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements