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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
barbforsman
Resolver I
Resolver I

Running Total by Month

I am brand new to Power BI Desktop and am LOVING it so far.

Except I am unable to correctly calculate a running total by month.  I have read and reread multiple posts about this already, and tried the formulats in the attached screenshot, but as you can see by the results for each formula tried, the totals do not accumlate month over month.  

Capture.JPG

 

My expected results are:

Month20162017 Total 
1 $    2,495.00 $    3,626.10 $      6,121.10
2 $    3,520.00 $  10,816.80 $    14,336.80
3 $    4,545.00 $  24,427.49 $    28,972.49
4 $    4,545.00 $  37,775.18 $    42,320.18
5 $    4,945.00 $  52,630.93 $    57,575.93
6 $    8,985.00 $  60,193.77 $    69,178.77
7 $  15,232.57 $  65,007.64 $    80,240.21
8 $  15,232.57 $  97,359.53 $  112,592.10

 

I have created a date table, dDateTable in the formulas and it has a 1:N relationship to my salesorder table.

 

Can you help me understand how to do this?  I apologize for repeating a question that has been asked before, but I just cannot get it to work on my own. 

 

Many thanks for your help!

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

Hi @barbforsman

 

Quick question when you are creating your tables, are you using the columns (Month, Year) from the Date table?

 

If you are using the columns from your Fact or table with the values then the calculations that you created will not work as expected.





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

Proud to be a Super User!







Power BI Blog

View solution in original post

8 REPLIES 8
GilbertQ
Super User
Super User

Hi @barbforsman

 

Quick question when you are creating your tables, are you using the columns (Month, Year) from the Date table?

 

If you are using the columns from your Fact or table with the values then the calculations that you created will not work as expected.





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

Proud to be a Super User!







Power BI Blog

That was it!!  Thank you so much!!

Awesome I have done that myself, and your calculations did look to be correct, well done!





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

Proud to be a Super User!







Power BI Blog

One more question - I need to reset the total each year so that each year starts from scratch.  I changed the slicer so it is using Date and Month from the dDateTable - is that not correct?

Current output. Column and Row are Month and Date from the dDateTableCurrent output. Column and Row are Month and Date from the dDateTable

 

Thank you again!

Hello all;

 

what if I have multiple tables (Actuals, Budget, Forecast) that are joined together using a date dimension table, how do I pull of of the cumulative for each table into one visual?

 

Thanks

Andrew

 

Have you tried creating a measure for each table that finds the cumulative value, then pulling the 3 measures into a visual using the year as a Colum (or Row, depending on your preference) and the the measure as the Values?

Thanks @barbforsman.

 

i have tried that but that does not seem to fix my issue. I can't use any date in my DateDimension table to pull up the cumulative for Actual, Budget and Forecast.

 

Here is an example of Cumulative Budget. It works as standalone when I use Budget Period as my axis but it does not work when I use [DateDimension].Fulldate or any other field from the DateDimension table. I can't seem to be able to post an image using the "Insert/Edit Image" button above to give you more details, though.

 

 

BudgetCumulative =
CALCULATE(
 SUM('Budget'[Budget $ (in '000s')]),
 FILTER(
  ALLSELECTED('Budget'[Budget Period]),
  ISONORAFTER('Budget'[Budget Period], MAX('Budget'[Budget Period]), DESC)
 )
)

 

Hi there

You can follow my blog post on how to easily post an image: https://www.fourmoo.com/2018/02/28/how-to-paste-a-screenshot-image-into-the-power-bi-community-forum...

I would suggest opening a new question as this one is already solved?




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

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors