Calculate Customer Purchasing in two distinct time periods

[DylanJohanssen]

Hello,

I have a table (Invoice) that has customer account number (customerAccountNumber), invoice total (TotalAmountBase) and date of purchase (InvoiceDate). I am trying to calculate a few columns to ultimately come up with a distinct count of customers and revenue that have purchased in the past R12 months and also in the R24-R12 month time period. I want this to dynamically change with the date. This is what I have:

R12DistinctCustomer = 

Var CurrentDate = Today()

Var R12Date = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

Var Result =

CALCULATE(

DISTINCTCOUNT(Invoice[customerAccountNumber]),

FILTER(invoice,Invoice[invoiceDate] >=R12Date && Invoice[invoiceDate] <= CurrentDate)

)

Return

Result

 My result for this measure is 3,469 distinct customers.

R12 Revenue = 

Var CurrentDate = Today()

Var R12Date = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

Var Result =

CALCULATE(

SUM(Invoice[totalAmountBase]),

ALLEXCEPT (invoice,Invoice[customerAccountNumber]),

FILTER(invoice,Invoice[invoiceDate] >=R12Date && Invoice[invoiceDate] <= CurrentDate)

)

Return

Result

 My total revenue for this column is $131,328,749

R24DistinctCustomer =

Var CurrentDate = Today()

Var R12Date = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

Var R24Date = DATE(Year(CurrentDate),Month(CurrentDate)-24,day(CurrentDate))

Var Result =

CALCULATE(

DISTINCTCOUNT(Invoice[customerAccountNumber]),

FILTER(invoice,Invoice[invoiceDate] >=R24Date && Invoice[invoiceDate] <= R12Date)

)

Return

Result

 my result for this column is 1,363 distinct customers

R24 Revenue = 

 

Var CurrentDate = Today()

Var R12Date = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

Var R24Date = DATE(YEAR(CurrentDate),Month(CurrentDate)-24,DAY(CurrentDate))

Var Result =

CALCULATE(

SUM(Invoice[totalAmountBase]),

ALLEXCEPT (invoice,Invoice[customerAccountNumber]),

FILTER(invoice,Invoice[invoiceDate] >=R24Date && Invoice[invoiceDate] <= R12Date)

)

Return

Result

 my result for this column is $24,471,456

I now want to create a column of distinct customers that have purchased in R12 Revenue time period AND the R24 Revenue time period which I created with this formula:

 R 12 AND 24 Revenue = 

IF(AND(Invoice[R12Revenue]>0,Invoice[R24Revenue]>0),SUMX(invoice,Invoice[R12Revenue] + Invoice[R24Revenue]),"0")

my result for this table is $155,800,204 which is the sum of the R12 Revenue column and the R24 Revenue column.

I would expect a figure equal to or lower than the R24 column number ($24,471,456) at the largest.This new column should be a union of the R12 and R24 columns if there are purchases in R12 time period and R24-R12 time period and instead it is including all entries whether they show up in R12 OR R24-R12.

I then need to create a column that would show me a distinct customer count of thenew R12 and 24 Revenue column that are > 0.

Help?

[amitchandak]

@DylanJohanssen , better to create measure in such case

Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

Rolling 12 before 12 Sales =
var _max1 = maxx(allselcted(date),date[date]) // or today()
var _max = date(year(_max1), month(_max1)-12,1)
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

For the new column, you can sum up the data will add for 12 month in each row

Rolling 12 Sales column =
var _max =[date] 
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

Rolling 12 before 12 Sales column =
var _max1 = [Date]
var _max = date(year(_max1), month(_max1)-12,1)
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

[DylanJohanssen · Accepted solution]

I was able to manage two new measures that compute R12 revenue and Previous R12 revenue. I then added the following measure to compute the total if a customer purchased in R12 and in Previous R12 time period. The new measure will return a zero if they did not purchase in BOTH timeframes. Just in case someone is looking to do the same.

R12ANDPRIORR12REVENUE =

IF(AND(Invoice[R12Revenue] >0,Invoice[PriorR12Revenue] >0), SUMX(invoice,Invoice[R12Revenue] + Invoice[PriorR12Revenue]),0)