Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |