cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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

Accepted Solutions
Highlighted
Community Support

## Re: Rolling 12 month sum with two filters

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
Highlighted
Super User IV

## Re: Rolling 12 month sum with two filters

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.

Highlighted
Super User IV

## Re: Rolling 12 month sum with two filters

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Community Support

## Re: Rolling 12 month sum with two filters

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.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors