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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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