Forum Discussion

SimoneOS's avatar
SimoneOS
Frequent Visitor
3 years ago
Solved

rolling 12 month calculation with group

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.FinanceSplit2Reporting Periods.FinMonthStartNet Rev
Group 202/04/2016£1,133,027.66
Group 302/04/2016£28,527.47
Group 102/04/2016£57,602.39
Group 303/04/2016£295,144.53
Group 203/04/2016£855,062.72
Group 103/04/2016£38,124.40
Group 201/05/2016£770,586.56
Group 301/05/2016£98,030.51
Group 129/05/2016£39,131.44
Group 229/05/2016£1,061,408.65
Group 329/05/2016£103,863.89
Group 203/07/2016£814,903.60
Group 303/07/2016£73,218.20

 

Can anyone help?

Need Help
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    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.

1 Reply

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.