Forum Discussion

rmalhan12's avatar
rmalhan12
Frequent Visitor
3 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
    3 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

rmalhan12's avatar
rmalhan12
Frequent Visitor
3 years ago

Any other suggestions would be greatly appreciated.