cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Last 12 Month Customer Sales in fact table

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Got the solution!

```L12M Sales = CALCULATE (
FILTER (
[Email_ID] = EARLIER ( OrderHeader[Email_ID] )

)
)```
20 REPLIES 20
Resolver I

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

Let me know if it works
Anonymous
Not applicable

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

Resolver I

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

Anonymous
Not applicable

Yes can't handle the volume of data

Anonymous
Not applicable

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
Not applicable

Got the solution!

```L12M Sales = CALCULATE (
FILTER (
[Email_ID] = EARLIER ( OrderHeader[Email_ID] )

)
)```
Resolver I

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

Regards,

Alejandro

Community Champion
```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
Not applicable

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

Community Champion

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
Not applicable

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

Community Champion

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
Not applicable

Hi,

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

Community Champion

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
Not applicable

18 million rows in total. 5 million distinct customers.

Community Champion

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

Anonymous
Not applicable

Hi,

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

Community Champion
```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
Not applicable

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```
Community Champion

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors