Calculation of new customers with a rolling avarage

[Anonymous]

Hi,

I am trying to calculate new customers with an avarage sales of 150k. Below is my new customers calculation which works fine. What I couldnt do is to add a rolling avarage calculation to the new customers calculation  and filter new customers above 150k:

New Customers

VAR c =

MIN ( customer[salesdate] )

RETURN

COUNTROWS (

EXCEPT (

VALUES ( customer[id] ),

CALCULATETABLE (

VALUES ( customer[id] ),

ALLSELECTED ( customer ),

customer[salesdate] < c

)

)

)

Rolling Average Sales=

AVERAGEX(

FILTER(ALLSELECTED(customer[salesdate]),

customer[salesdate] <= MAX(customer[salesdate])),

[totalsales])

Thanks in advance for the answers!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Hello,

Thanks for asnwer.

I have a table as below.

Customer_id	billing_date	revenue
Whole Number	date	whole number

I need to calculate 2 conditions to decide customers current situation.

Condition A: checks whether the customer purchased in last 2 fiscal year.

Condition B: finds the last purchases in last 2 fiscal year and calculates an avarage. the avarage revenue per customer should be less than 100k

Flag: (yes or no) :if the condition A and Condition B is okey flag returns yes.

Thank you very much for help

@Ashish_Mathur 

[Ashish_Mathur]

Hi,

What is the span of the fiscal year.  If a user selects September 2020, then what should the beginning of the 2 year fiscal period be?

[Anonymous]

Hi,

Fiscal year starts with 1st of November. If the user selects September 2020 then the the code needs to check back to November 2018.

Thank you @Ashish_Mathur 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.