Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 ) )
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
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
Does this also give you performance issues? I modeled it with your sample data and it worked
Yes can't handle the volume of data
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.
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 ) )
Great!! I don't think you should run into any performance issues with this approach...
Regards,
Alejandro
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)
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
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!
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
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)
Hi,
Thanks for the revised formula. It's struggling with the volume of data. Anyway to make it more efficient?
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.
18 million rows in total. 5 million distinct customers.
Aight, well it's not impossible! Try converting to numeric first and see if it works!
Hi,
Converting the date to numeric hasn't worked, still getting the memory issue. I'm running on 16gb RAM
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?
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
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?