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
mhomol
Helper I
Helper I

Rolling totals into a calculation

I have columns and calculated columns that are summarizing information for a particular year, like so:

 

YearPrevious TotalIncomeContributionsExpensesTotal
2018 105(2)13
201913150028
20202857(5)35

 

I want to include the total for one year in the calculation of another total.  Is there a way I can do this without generating a circular reference?

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@mhomol although @Fowmy  solution will work I will try to recycle measures as much as possible, add following 3 measures

 

//even this should be further divided into 3 measures one for each column
Total = SUM ( RT[Income] ) + SUM ( RT[Contributions] ) + SUM ( RT[Expenses] )

Closing = CALCULATE ( [Total], FILTER ( ALL ( RT[Year] ), RT[Year] <= MAX ( RT[Year] ) ) )

Opening = 
VAR __prevYear = MIN ( RT[Year] ) - 1 
RETURN CALCULATE ( [Closing], RT[Year] = __prevYear ) 

 

Also, we want to make sure visually it adds up at the Total line:

 

parry2k_0-1623617122243.png

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

11 REPLIES 11
parry2k
Super User
Super User

@mhomol why you need to do this? Closing is already taking care of it. not sure what you are trying to solve here



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k , the report I'm working on is for a customer that will use the report to analyze year over year returns for tax purposes.  The previous year's final number needs to be the starting point that all calculations are based on for the next year.  

 

So I think my first big mistake has been with relying on calculated columns more than measures.  Thank you for that advice.  

 

I see that the table in the report you provided is somehow including the Opening in it's Total.  I'm not clear on how it's doing that though.  Maybe this is at the heart of my misundersanding of things.  I don't see it included in the calculation for Total.  How is it the that the Closing formula manages to include it?

@parry2k , thanks for working with me so much.  It took me longer than it should have but I understand now how your measures were working.  It's a different way of thinking about the data than I'm used to, but it makes sense.

parry2k
Super User
Super User

@mhomol Yes, please add as a measure. You have to be careful and must have a reason why you want to add it as a column. There are lot more articles on Measure vs calculated columns, and you can read about those when you have time.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@mhomol look at page 4, table RT, there are other tables in the file, just ignore those.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k , So the big thing that's missing in yours that I'm trying to do in mine, which generates the circular dependency issue, is that I need the Opening value including in the calculations going forward.  It's almost like I need a "copy" of the closing value from the previous period or something like that.  I was able to replicate the issue even in your in the following way:

Screen Shot 2021-06-14 at 1.26.22 PM.png

 

Am I just trying to do something that is not supported in Power BI?

@parry2k the big difference I see right away is the fact that you did a measure (?) and I did a column.  Should I be trying to do this kind of stuff as a measure?

parry2k
Super User
Super User

@mhomol although @Fowmy  solution will work I will try to recycle measures as much as possible, add following 3 measures

 

//even this should be further divided into 3 measures one for each column
Total = SUM ( RT[Income] ) + SUM ( RT[Contributions] ) + SUM ( RT[Expenses] )

Closing = CALCULATE ( [Total], FILTER ( ALL ( RT[Year] ), RT[Year] <= MAX ( RT[Year] ) ) )

Opening = 
VAR __prevYear = MIN ( RT[Year] ) - 1 
RETURN CALCULATE ( [Closing], RT[Year] = __prevYear ) 

 

Also, we want to make sure visually it adds up at the Total line:

 

parry2k_0-1623617122243.png

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k , are you able to share your pbix?  I used a simple example instead of my more complex one and I'm still getting circular reference error with mine.  I thought I would take a look at yours to see if what i'm doing in addition that might be creating the issue.

Hi,

This can be done if we have a proper Calendar Table.  To get more help, share some data.


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

@mhomol 

Hope you want to show it in a table or matrix visual. Add the following two measures:

Previous = 
var __year = SELECTEDVALUE(Table22[Year]) 
return
SUMX( 
    FILTER( ALL(table22) , Table22[Year] < __year ) , 
    Table22[Income] + Table22[Contributions]  + Table22[Expenses]
)
Closing = 
var __year = SELECTEDVALUE(Table22[Year]) 
return
SUMX( 
    FILTER( ALL(table22) , Table22[Year] <= __year ) , 
    Table22[Income] + Table22[Contributions]  + Table22[Expenses]
)

Fowmy_0-1623615500604.png

 







Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.