Value sum for last N months

[Waseem]

Dear Community

I have four columns in a Table namely "Customer Code, "Date", "Activation Date" and "Revenue". The "Date" filed represents last month and date ( in MM-DDD-YYYY format as I receive monthly data dump after a month is closed). "Activation Date" contain multiple dates starting from previous month to (say) 8 years ado showing activation date of respective customers. The "Revenue" filed contains monthly revenue against each customer code. 

I intend to count custoer codes and sum Revenue fileds for N number of months including last month. So the objective is to show during last (say) 3 months how many customers were activate and how much revenue was generated.

Appreciate your help.

Cheers

[v-frfei-msft]

Hi @Waseem,

We can take the following steps to meet your requirement.

1. Create a  date table using the formula.

date = 
var mind = MIN('Value'[Date])
var minas =MIN('Value'[Activation Date])
var maxd = MAX('Value'[Date])
var maxas = MAX('Value'[Activation Date])
return 
CALENDAR( IF(mind<minas,mind,minas), IF(maxd>maxas,maxd,maxas))

2. Create the relationship between the data table and value table like this.

3. Create the measures as below to get the result as we excepted.

active1 = CALCULATE(COUNTROWS(DISTINCT('Value'[Customer Code])),USERELATIONSHIP('date'[Date],'Value'[Activation Date]),DATESINPERIOD('date'[Date],MAX('date'[Date]),-3,MONTH))

sumprevious3month1 = CALCULATE(SUM('Value'[Revenue]),DATESINPERIOD('date'[Date],MAX('date'[Date]),-3,MONTH))

For more details, please check the pbix as attached. If it doesn’t meet your requirement, kindly share you sample data and excepted result to me.

https://www.dropbox.com/s/upy5msawrbcsjwj/Value%20sum%20for%20last%20N%20months2.pbix?dl=0

Regards,

Frank

[Waseem]

Thanks a lot @v-frfei-msft for the kind help. The results were not exactly match due to a parameter in my date field that i did not explain earlier (apologize for that). The table snapshot is as follows. Actually my "date" field is one date for all values depicting the month, data snapshot is taken for. The objective is to count customer codes and to have sum of revenues for customers having activation year and month same that appears in "date" filed PLUS for previous three months. For example, the date says 2018-07-Jul. So I would like to take count of customer codes and sum of revenue for those entries that have activation date in Jul 2018 and N previous months. Since i update my data every month, "date" fileds needs to be dynamic. Appreciate your help. Cheers

Table

[v-frfei-msft]

Hi @Waseem,

We can create the relationship between Value and date table like this and create two measure later to meet your requirement.

active1 = CALCULATE(COUNTROWS(DISTINCT('Value'[Customer Code])),DATESINPERIOD('date'[Date],MAX('date'[Date]),-3,MONTH))

sumprevious3month2 = CALCULATE(SUM('Value'[Revenue]),DATESINPERIOD('date'[Date],MAX('date'[Date]),-3,MONTH))

For more details, please check the pbix as attched.

https://www.dropbox.com/s/9dggqofwgja1e5k/Value%20sum%20for%20last%20N%20months3.pbix?dl=0

Regards,

Frank

[v-frfei-msft]

Hi @Waseem,

Does that make sense, If so, kindly mark my answer as a solution to close the case please.

Regards,

Frank

[Waseem]

Hi @v-frfei-msft

let me put it in excel terms. Actually I intend to filter the Activation Date column with reference to Date column. If Date column is for July, I intend to filter Activation Date column for Jul, June and May. i.e. pick date and year from column Date and filter for three months from Activation Date column. Appreciate your help.

Regards  

[Waseem]

Hello @v-frfei-msft

Sorry i am onn vacatons these days so couldnt check my email. Actually in your formula of active1, there is no reference to "Activation Date". I need to take customer count and sum of revenues for those customers whose activation date in "Activation Date" filed is same as in "Date" filed of same table plus those customers who have their activation dates 2 months before that appears in "date" field. Thats how in total it would become three months. For example "Date" filed in my table is 7 Jul 2018. I want to filter "Activation Date" for those customers whose activation date is July 2018 (the Date filed), June 2018 and May 2018. I would then count these customers and sum their revenues. Hope i clarified it well this time.

Cheers