rolling 12 month calculation with group

[SimoneOS]

I need to create a rolling 12 month calculation - which ive done and it works but i need it to split by groups and not sure how to do this bit

Rolling12M = 
Var numofmonth = 12
Var Lastselecteddate = max('Reporting Periods'[Date Link])
Var Period = 
    DATESINPERIOD('Reporting Periods'[Date Link], Lastselecteddate, -numofmonth,MONTH)
var Result =
    CALCULATE(
        AVERAGEX(
            VALUES('Reporting Periods'[Calendar year month number]),
            [NetRevM]
        ),
        Period)
Var lastdatewithsale = max(RollingTable[Reporting Periods.FinMonthStart])
 Var firstvisibledate = MIN('Reporting Periods'[Date Link])
        Return
        if(firstvisibledate <=lastdatewithsale, Result)

the data is structured:

vwFcstCustomer.FinanceSplit2	Reporting Periods.FinMonthStart	Net Rev
Group 2	02/04/2016	£1,133,027.66
Group 3	02/04/2016	£28,527.47
Group 1	02/04/2016	£57,602.39
Group 3	03/04/2016	£295,144.53
Group 2	03/04/2016	£855,062.72
Group 1	03/04/2016	£38,124.40
Group 2	01/05/2016	£770,586.56
Group 3	01/05/2016	£98,030.51
Group 1	29/05/2016	£39,131.44
Group 2	29/05/2016	£1,061,408.65
Group 3	29/05/2016	£103,863.89
Group 2	03/07/2016	£814,903.60
Group 3	03/07/2016	£73,218.20

Can anyone help?

[v-yinliw-msft · Accepted solution]

Hi @SimoneOS ,

There are two methods:

1. You can use slicer:

2.You can try this DAX:

Group1 = CALCULATETABLE('Table','Table'[vwFcstCustomer.FinanceSplit2] = "Group 1")

Group2 = CALCULATETABLE('Table','Table'[vwFcstCustomer.FinanceSplit2] = "Group 2")

Group3 = CALCULATETABLE('Table','Table'[vwFcstCustomer.FinanceSplit2] = "Group 3")

The result is:

Hope these help you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.