Rolling 12 month sum with two filters

[Zabeer]

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

[dax · Accepted 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.

[Mariusz]

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.
LinkedIn

[parry2k]

@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