Forum Discussion

rmalhan12's avatar
rmalhan12
Frequent Visitor
4 years ago
Solved

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 dif...
Need Help
Show and Tell
Tips and Tricks
  • MahyarTF's avatar
    MahyarTF
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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