Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |