cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tracytran91
Helper II
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. 

sum.png

 

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

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!

View solution in original post

12 REPLIES 12
v-xulin-mstf
Community Support
Community Support

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:

v-xulin-mstf_0-1623224598612.png

 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.

 

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

sum.png

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?

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!

View solution in original post

Ashish_Mathur
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/

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

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
https://www.linkedin.com/in/excelenthusiasts/

There is the error for this measure. 😞 

sum.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Helper II
Helper II

@amitchandak 

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

sum.png

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors