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.
Hello. I am struggling with a particular dax expression for the below problem. I would like to create a calculated column that gives the rolling 12 month sum per customer per category. There are many more customers and categories.
Sample data :
Customer | Category | Date | Value | Expected result (rolling 12 month sum) |
A | F | 01/01/2019 | 10 | 10 |
A | F | 01/02/2019 | 2 | 12 |
A | F | 01/03/2019 | 7 | 19 |
A | F | 01/04/2019 | 4 | 23 |
A | F | 01/05/2019 | 3 | 26 |
A | F | 01/06/2019 | 4 | 30 |
A | F | 01/07/2019 | 5 | 35 |
A | F | 01/08/2019 | 2 | 37 |
A | F | 01/09/2019 | 6 | 43 |
A | F | 01/10/2019 | 3 | 46 |
A | F | 01/11/2019 | 1 | 47 |
A | F | 01/12/2019 | 5 | 52 |
A | F | 01/01/2020 | 2 | 44 |
A | F | 01/02/2020 | 8 | 50 |
Solved! Go to Solution.
Hi @Zabeer ,
You also could refer to below measure to see whether it work or not
Measure =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date]
> DATE ( YEAR ( MIN ( 'Table'[Date] ) ), MONTH ( MIN ( 'Table'[Date] ) ) - 12, 1 )
&& 'Table'[Date] <= MIN ( 'Table'[Date] )
)
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Zabeer ,
You also could refer to below measure to see whether it work or not
Measure =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date]
> DATE ( YEAR ( MIN ( 'Table'[Date] ) ), MONTH ( MIN ( 'Table'[Date] ) ) - 12, 1 )
&& 'Table'[Date] <= MIN ( 'Table'[Date] )
)
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Zabeer
You can use DATESINPERIOD
https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax
Please see the DAX Mesure example below.
Sales Rolling 12 months =
CALCULATE(
[Sales],
DATESINPERIOD( 'Calendar'[Date], MIN( 'Calendar'[Date] ) -1, -12, MONTH )
)
@Zabeer similar posts on it and you can change it as per your business rules
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
https://www.accountingweb.co.uk/business/finance-strategy/power-bi-rolling-12-months-and-ytd
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |