Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
rmalhan12
Frequent Visitor

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

1 ACCEPTED SOLUTION
MahyarTF
Memorable Member
Memorable Member

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
MahyarTF_0-1662111108583.png

Appreciate your Kudos

Mahyartf

View solution in original post

6 REPLIES 6
rmalhan12
Frequent Visitor

Any other suggestions would be greatly appreciated. 

MahyarTF
Memorable Member
Memorable Member

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
MahyarTF_0-1662111108583.png

Appreciate your Kudos

Mahyartf

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. 

Hi,

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

Mahyartf
rmalhan12
Frequent Visitor

rmalhan12_0-1662109578200.png

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

 

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.