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
maurcoll
Helper II
Helper II

Rolling 12 month total

Hi

I am trying to calculate the last rolling 12 months data that i can put into a line chart visual.

It will need to show monthly going back for 12 months as per the below data. So for Sept 23 it will show the sum of data for the October 22 - Sept 23. August 23 will show data for Sept 22 - Aug 23 and so on.

 

TotalMonthYearRolling 12 months
4001/01/2022 
7801/02/2022 
13601/03/2022 
27501/04/2022 
36001/05/2022 
46301/06/2022 
52201/07/2022 
61201/08/2022 
68701/09/2022 
76101/10/2022 
83801/11/2022 
88701/12/20225659
90601/01/20236525
95101/02/20237398
97401/03/20238236
90801/04/20238869
88001/05/20239389
87601/06/20239802
89001/07/202310170
88701/08/202310445
88101/09/202310639
 
This is the calculation i am using but it is showing monthly totals on the chart not the cumulated 12 month total.
Rolling 12 Months =
CALCULATE(
    SUM( Total[Total]),
    DATESINPERIOD( Date[MonthYear], MAX( Date[MonthYear] ), -12,  MONTH )
2 ACCEPTED SOLUTIONS
jgeddes
Super User
Super User

A measure like the following might work for you...

rollingTotalMeasure =
var _rollingMonths =
12
var _maxDate =
MAXX(
    ALL('Table'),
    [MonthYear]
)
Return
CALCULATE(
    SUM('Table'[Total]),
    FILTER(
        ALL('Table'),
        [MonthYear] <= MAX('Table'[MonthYear]) && DATEDIFF([MonthYear], _maxDate, MONTH) < _rollingMonths
    )
)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1702701820998.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1702701820998.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur   Ia m trying to achieve rolling 12 month total like Runways by forecast..This logic is not helping me in powerbi. The highlighted column is achived using excel spreadsheet and row numbers.I want to achive the same using DAX in powerbi.

Runway by CF for Nov = Total of current forecast - current forecast of Nov

Runway by CF for Dec and so on= Runway by CF for Nov- current forecast of Dec   and so on.

 

Can you please advise if you can gte breakthrough for this logic. 

 

 

ashish_18_0-1721679832247.png

 

Hi,

I cannot understand anything there at all.  Share an MS Excel file with your Excel formulas.  I will convert those Excel formulas into DAX measures.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, unable to add excel here but can paste values frome excel as below.

for Nov , =SUM(C2:C13)-C2

for dec  =SUM(C3:C13)-C3

for Jan, =SUM(C4:C14)-C4

and so on

ashish_18_0-1721700875864.png

 

Appreciate your help here,,

 

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1721877988268.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

hi @Ashish_Mathur   thanks for ur help...However I could not read ur pbix due to latest verison of pbi desktop..Anyway I have sorted out that part and my runway by CF is working... 

 

Can you please help me to convert another excel formulas in DAX for runway calculation

Actual requiremnt is as below.

 

Runway by Burnrate for Nov = Total of current cost - Nov cost i.e.( C2:C13)-C2
Runway by Burnrate for Dec =Runway by Burnrate for Nov - burn rate of Nov i.e F2-E2
Runway by Burnrate for Jan =Runway by Burnrate for Dec - burn rate of Dec i.e F3-E3
and so on

 

(C:C ) means All rows of column "current cost". I taken that to take total of that column and minus current cost of Nov (C2)

 

Actual excel for "AAA" is as below but in main source file AAA,BBB,CCC are added as column "FI name" so runway and burn rate should work when we select AAA,BBB,CCC from slicer.

 

ashish_18_0-1722268563453.png

 

 

Hi,

I cannot and will not work with just an image.  Share the download link of the MS Excel file with your formulas already written there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ThxAlot
Super User
Super User

ThxAlot_0-1702671721661.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



jgeddes
Super User
Super User

A measure like the following might work for you...

rollingTotalMeasure =
var _rollingMonths =
12
var _maxDate =
MAXX(
    ALL('Table'),
    [MonthYear]
)
Return
CALCULATE(
    SUM('Table'[Total]),
    FILTER(
        ALL('Table'),
        [MonthYear] <= MAX('Table'[MonthYear]) && DATEDIFF([MonthYear], _maxDate, MONTH) < _rollingMonths
    )
)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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!

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.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.