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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
U156531
Post Patron
Post Patron

How to calculate a rolling total

 I need to get a rolling total by month for the Result below. Can someone show me how to accomplish this. I'm currently using a slicer for the dates.

         ValueA                ValueB         Result

Jan         5                        2                 7

Feb        3                        1                 11

Mar       4                        1                 16

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @U156531 ,

 

A calendar table is needed for the model.

vcgaomsft_1-1719205439574.png

And please create a new measure like:

Result = 
CALCULATE (
    SUM ( 'TableA'[ValueA] ) + SUM ( 'TableB'[ValueB] ),
    'Calendar'[Month Num] <= MAX ( 'Calendar'[Month Num] ),
    REMOVEFILTERS ( 'Calendar'[Month] )
)

vcgaomsft_0-1719205377911.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

Hi @U156531 ,

 

A calendar table is needed for the model.

vcgaomsft_1-1719205439574.png

And please create a new measure like:

Result = 
CALCULATE (
    SUM ( 'TableA'[ValueA] ) + SUM ( 'TableB'[ValueB] ),
    'Calendar'[Month Num] <= MAX ( 'Calendar'[Month Num] ),
    REMOVEFILTERS ( 'Calendar'[Month] )
)

vcgaomsft_0-1719205377911.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

kpost
Super User
Super User

result.PNG

 

I produced this table using only the first 3 columns.   Your question is too vague to give specific advice, but:

 

BIG PICTURE, you need to create a measure that changes filter context, specifically around the date value.

I've done it in a Power BI file, attached,  with the following measure.  The exact structure of this is could be wildly different depending on your situation (no information provided about your data model, no sample data, no .pbix file etc), however, again, BIG PICTURE, you want to modify the filter context.

Result =
VAR max_rowdate = max('Table'[Date])
RETURN
CALCULATE(COALESCE(SUM('Table'[ValueA]) + SUM('Table'[ValueB]), 0), ALL('Table'), 'Table'[Date] <= max_rowdate)

See attached .pbix file.  Let me know if you have any questions.  Good luck!

///Mediocre Power BI Advice, but it's free///
 

I'm getting this error. Percentcol is a calculated column.

 

U156531_0-1718139042659.png

 

With more information about your data tables and model, I could be much more helpful.

If you could create a sample .pbix file re-creating the issue you are experiencing, that would be the absolute best case scenario.  Way less back-and-forth, and plus i can just upload a .pbix file containing the solution.

I have an example pbix file to share but I don't seem to have the abilty to attach it or upload it to a shared location. My organization blocks the sites like dropbox. Is there another method I can use?

https://catbox.moe

 

I don't necessarily "endorse" the site, and I would certainly *never* upload something confidential to it, but it is (one of many) free and easy to use sites where you can just upload a file then provide us the link, without making an account.

 

I've also seen people have success with Google Drive, if you have an account with them.

Kpost, I just wanted to be sure you saw my reply from yesterday. 

Yes, I did see your message.  I am working through some pressing deadlines at work.  I'll be back to help when I have some free time, possibly tomorrow.  Rest assured the notifications regarding this post are still going to my email inbox.

Since I can't send it. I've modified your original pbix to fit my situation.  I changed it to use two tables in the Result measure. Are you able change the Result measure to produce the correct results without using manage relationships to link the dates?  Thanks

U156531_0-1718215844568.png

 

Unfortunately there is too much confidential information in the report and many tables. I think if you took a look at the pbix you would get lost in the way it has been patched together. It doesn't match to the sample I sent in my original post because I was trying to simplify my question.

Here is the calculated column that the error is refering to.

PercentCol = ('Merge1'[Quantity Received for Current Year] + 'RFID/Barcode Defective Material Submittals'[QuantityM])/ ('Adjusted Multiplier Revised 2022'[TragetWithBradley])

 

 

 

 

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors