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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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