cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Rolling 12 month sum with two filters

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

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Community Champion

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 )
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Super User

@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/

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors