Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
119 | |
105 | |
77 | |
74 | |
52 |
User | Count |
---|---|
145 | |
109 | |
107 | |
90 | |
64 |