Rolling 12 Months per Unique Reference - Power Query

[rmalhan12]

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

[rmalhan12]

Any other suggestions would be greatly appreciated. 

[MahyarTF · Accepted solution]

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 _TotalLast

Appreciate your Kudos

[rmalhan12]

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. 

[MahyarTF]

Hi,

You could create the column with same code an it is one of the table's coumn

[rmalhan12]

I get the following error message, the reference begins with letters

[amitchandak]

@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])