Average last 3 Months with Distinct Value

[tracytran91]

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!

[v-xulin-mstf]

Hi @tracytran91

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,
Link

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

[tracytran91]

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

Sample data here: 

https://drive.google.com/file/d/1mQzTZ53zI_3EJ4okIpNJCmC9G-IXOxDF/view?usp=sharing

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?

[v-xulin-mstf · Accepted solution]

Hi @tracytran91,

Sorry for delay reply.

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,

Link

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

[Ashish_Mathur]

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.

[tracytran91]

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

[Ashish_Mathur]

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

[tracytran91]

There is the error for this measure. 😞 

[Ashish_Mathur]

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])

If this does not help, then share the link from where i can download your PBI file.

[tracytran91]

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

Here is the link : https://drive.google.com/file/d/1LFQWqvLV5cwDfWzcnjzNR5I6W4K1ZV7I/view?usp=sharing

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.

[tracytran91]

@amitchandak 

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

[amitchandak]

@tracytran91 ,

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]))))))

[Ashish_Mathur]

Hi,

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