Forum Discussion
Rolling 12 Months per Unique Reference - Power Query
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
Hi,
Create the below measure in your table :
YTD Income =Var _LastDate = LASTDATE(Sheet155[Report Date])Var _TotalLast = CALCULATE( sum(Sheet155[Income]),ALLEXCEPT(Sheet155,Sheet155[Ref]),DATESBETWEEN(Sheet155[Report Date], _LastDate - 365, _LastDate))return _TotalLastAppreciate your Kudos
6 Replies
Hi,
Create the below measure in your table :
YTD Income =Var _LastDate = LASTDATE(Sheet155[Report Date])Var _TotalLast = CALCULATE( sum(Sheet155[Income]),ALLEXCEPT(Sheet155,Sheet155[Ref]),DATESBETWEEN(Sheet155[Report Date], _LastDate - 365, _LastDate))return _TotalLastAppreciate your Kudos
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])I get the following error message, the reference begins with letters
Any other suggestions would be greatly appreciated.
