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

MahyarTF's avatar
MahyarTF
Memorable Member
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

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's avatar
    MahyarTF
    Memorable Member
    3 years ago

    Hi,

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