Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am aiming to build a custom column which calculates the total rolling 12 months based on a unique reference. I want it to return the values by each row in the 12 month column. I have tried many different ways to no success. Would like some assistance/guidance. Thanks in advance.
Report Date Ref Income Rolling12MonthIncome
30/12/20 1025 £20 return the Income column total where the Ref column is "1025" based on 12 month report date
30/11/20 1029 £30 return the Income column total where the Ref column is "1029" based on 12 month report date
30/10/20 1025 £50 return the Income column total where the Ref column is "1025" based on 12 month report date
Solved! Go to Solution.
Hi,
Create the below measure in your table :
Appreciate your Kudos
Any other suggestions would be greatly appreciated.
Hi,
Create the below measure in your table :
Appreciate your Kudos
Is there a method to build it into the actual data table as it is more dynamic. Above method I believe is on a visualisation as I have further conditional analysis to do after this step.
Hi,
You could create the column with same code an it is one of the table's coumn
I get the following error message, the reference begins with letters
@rmalhan12 , Try a new column
A New column =
var _max = eomonth([Report Date], 0)
var _min = eomonth([Report Date], -12) +1
return
sumx(filter(Table, [Ref] =earlier([Ref]) && [Report Date] <= _max && [Report Date] >= _min), [Income])
User | Count |
---|---|
115 | |
95 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |