Last 12 Month Customer Sales in fact table

[Anonymous]

Hi all,

I have a standard fact table containing the following fields:

OrderDate

Customer_ID

SalesValue

Product_ID

I'm trying to create a calculated column which gives me the last 12 months sales for the customer for each record in the fact table. The calculated column needs to be dynamic based on each orderdate, so would look something like this:

OrderDate	Customer_ID	Product_ID	SalesValue	L12M Sales
01/01/2017	1000	100	50	125
01/01/2017	1000	101	75	125
05/07/2018	1000	100	50	175
01/01/2019	1000	100	50	175
01/01/2019	1000	101	75	175
07/05/2019	1000	100	50	400
07/05/2019	1000	101	75	400
07/05/2019	1000	102	100	400

The table would have different customer id's so would need to be able to identify different customers.

I'm really struggling so any help would be appreciated.

Thanks

Mike

[alexvc]

Have you revised your approach?

I think is convenient for you to have a separate summarized table of customer order totals per date (without line detail). This table would have to be something like OrderHead = SUMMARIZE(OrderDtl,OrderDtl[Customer_ID],OrderDtl[OrderDate],"Sales",sum(OrderDtl[SalesValue]))

You would have to create a relationship between this summarized table and the detailed table through a concatenated Orderdate&customerID column. After this you could create the calculated field with a formula like 

Calc Last 12M = CALCULATE(sum(OrderHead[Sales]),DATEADD(OrderDtl[OrderDate],-1,YEAR),filter(OrderHead,OrderHead[OrderDate]<=OrderDtl[OrderDate]))

Let me know if it works

[Anonymous]

Hi,

Unfortunately that hasn't worked either. I've even reduced the size of my fact table down to 13 million rows now and still no joy.

Mike

[alexvc]

Does this also give you performance issues? I modeled it with your sample data and it worked

[Anonymous]

Yes can't handle the volume of data

[Anonymous]

I think I'm getting close. I've created the summarised table and created the below calcualted column. I just can't figure out how to adapt the formula in the calcualted column to give me the last 12 month sales value for each row.

[Anonymous · Accepted solution]

Got the solution!

L12M Sales = CALCULATE (
    SUM ( OrderHeader[Sales] ),
    FILTER (
        ALL ( OrderHeader ),
        [Email_ID] = EARLIER ( OrderHeader[Email_ID] )
            && EARLIER(OrderHeader[OrderDate]) >= OrderHeader[OrderDate]
            && OrderHeader[OrderDate] >= EARLIER(OrderHeader[OrderDate])-365
            
            
    )
)

[alexvc]

Great!! I don't think you should run into any performance issues with this approach...

Regards,

Alejandro

[tex628]

R12 = 
VAR mdate = MAX('Calendar'[Date])
VAR myear = YEAR(mdate)
VAR mmonth = MONTH(mdate)
VAR mday = DAY(mdate)
VAR minDate = DATE(myear-1;mmonth;mday)

Return
CALCULATE([Amount];ALL('Calendar');'Calendar'[Date]>minDate;'Calendar'[Date]<=mdate)

[Anonymous]

Hi,

Thanks I've tried this but it isn't dynamic based on the orderdate in the record and also doesn't take into account the fact table will have different customer id's.

How can the formula be adapted for this?

Thanks

Mike

[tex628]

If you place it in a matrix with the orderdate and the customer, the MAX(Calendar[date]) should return the date that is on the row and customer should apply a row context filter to the measure aswell meaning that the extression is being evaluated with a customer filter aswell. 

If you make a measure that is just MAX(Calendar[date]) and it returns something other than your orderdate on the record there is something wrong and this wont work! 

Let me know how it goes!

[Anonymous]

Hi,

Placing the customer in a table isn't an option as I have over 5 million of them.

I want the solution to be a calcualted column so I can create a dynamic segmentation using the last 12 months sales by customers (so i can see how customers move through the segments).

Regards

Mike

[tex628]

Ahh thought you were doing a measure. In that case:

R12 = 
VAR cust = Table2[Customer]
VAR mdate = Table2[OrderDate]
VAR myear = YEAR(mdate)
VAR mmonth = MONTH(mdate)
VAR mday = DAY(mdate)
VAR minDate = DATE(myear-1;mmonth;mday)
return
CALCULATE(SUM(Table2[Amount]);ALL(Table2);Table2[Customer] = cust; Table2[OrderDate]>minDate; Table2[OrderDate]<=mdate)

[Anonymous]

Hi,

Thanks for the revised formula. It's struggling with the volume of data. Anyway to make it more efficient?

[tex628]

Do you have 5 million rows in total or do you have 5 million distinct customers? In that case, how much are we talking in total? 

What you could do is convert your OrderDate to numeric format instead of date format, this should lighten the load abit. 

[Anonymous]

18 million rows in total. 5 million distinct customers.

[tex628]

Aight, well it's not impossible! Try converting to numeric first and see if it works! 

[Anonymous]

Hi,

Converting the date to numeric hasn't worked, still getting the memory issue. I'm running on 16gb RAM

[tex628]

R12 = 
VAR cust = Table2[Customer]
VAR mdate = Table2[OrderDate]
VAR minDate = mdate-365
return
CALCULATE(SUM(Table2[Amount]);ALL(Table2);Table2[Customer]=cust; Table2[OrderDate]>minDate; Table2[OrderDate]<=mdate)

I'm guessing what you tried is something like this? 
 

[Anonymous]

Yes, also tried this:

R12 = 
VAR CurrentDate = Orders[OrderDate]
VAR PreviousDate = Orders[OrderDate]-365
VAR Customer = Orders[Email_ID]
VAR Result = 
CALCULATE(
    SUM(Orders[SalesValue]),
    FILTER(
        Orders,
        Orders[OrderDate]>=PreviousDate && 
        Orders[OrderDate] <= CurrentDate &&
        Orders[Email_ID] = Customer
    )
)

RETURN

Result

[tex628]

I have some thoughts and ideas but i honestly doubt that they will work. 

@Greg_Deckler , Do you happen to have any ideas on creating a calculated rolling 12 column on a 18M row table?