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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Average of beginning and ending total assets

Hi,

I need a formula to pull the values for 12/31 of the prior year based on the selected month end date.  I'm trying to do a formula for return on assets and the denominator requires the average of the beginning and ending total assets.  So if I select August 2021, the formula should pull in the total assets from August 2021 and December 2020 to do the average.  If I select June 2020, the formula should pull in June 2020's total assets and December 2019's.  I have the following but that is only pulling in the balance from last year based on the date selected and not December.  Please let me know.  Thanks.

Average of Total Assets = CALCULATE(AVERAGEX(VALUES('Date'[Mo Cal Yr]),[Total Assets]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-1,YEAR))
1 ACCEPTED SOLUTION
Super User

Hi @rwong1

Try this:

``````Average of Total Assets =
VAR _MSD =
MAX ( 'Date'[MonthID] )
VAR _YSD =
MAX ( 'Date'[Year] )
VAR _LYED = _YSD - 1
VAR _MED = 12
VAR _A =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
FILTER ( ALL ( 'Date' ), 'Date'[MonthID] = 12 && 'Date'[Year] = _LYED )
)
VAR _B =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
FILTER ( ALL ( 'Date' ), 'Date'[MonthID] = _MSD && 'Date'[Year] = _YSD )
)
RETURN
_A + _B``````

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn:
www.linkedin.com/in/vahid-dm/

11 REPLIES 11
Helper III

Hi,

Yes I do.  Please see below a screenshot:

Super User

Hi @rwong1

Try this:

``````Average of Total Assets =
VAR _SD =
MAX ( 'Date'[Date] )
VAR _LYED =
DATE ( YEAR ( _SD ) - 1, 12, 31 )
RETURN
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
FILTER ( ALL ( 'Date' ), 'Date'[Date] > _LYED && 'Date'[Date] <= _SD )
)``````

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn:
www.linkedin.com/in/vahid-dm/

Super User

Hi @rwong1

Try this:

``````Average of Total Assets =
VAR _MSD =
MAX ( 'Date'[MonthID] )
VAR _YSD =
MAX ( 'Date'[Year] )
VAR _LYED = _YSD - 1
VAR _MED = 12
VAR _A =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
FILTER ( ALL ( 'Date' ), 'Date'[MonthID] = 12 && 'Date'[Year] = _LYED )
)
VAR _B =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
FILTER ( ALL ( 'Date' ), 'Date'[MonthID] = _MSD && 'Date'[Year] = _YSD )
)
RETURN
_A + _B``````

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn:
www.linkedin.com/in/vahid-dm/

Helper III

Thanks.  This was correct.  I just needed to divide the sum by 2.

Helper III

Hi

I just tried that and it's still calculating incorrectly.  Is it because I have another measure that is the below?

Return on Assets = [Net Profit]/[Average of Total Assets]
Super User

Can you post sample data as text and expected output?

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Helper III

Ok.  My total assets at 12/31/20 was 97,617,194.  My total assets at 10/31/21 was 123,224,867.  If I take the average of the 2, it comes out to 110,421,004.  The net income from Jan to Oct of 2021 was 4,491,961.  The return on assets should then be net income divided by average of the total assets which comes out to 110,421,004.  Therefore my return on assets should be 4.07%.  When using what you gave me, it came up to 2.03%.  The report I'm using is the one that formed the financials so I know it works.

Helper III

Thanks for that.  I put that in there and it didn't give me the correct calculation.  Is there something else that I have to tweak?

Thanks.

Helper III

I want to emphasize that the total assets should only be from those 2 months.  I don't need anything pulled in between.  So if I select August 2021 from the slicer, it should only pull in August 2021 and December 2020 total assets to do the average, not from December 2020 thru August 2021.

Super User

Hi @rwong1

Do you have Month and Year columns in the Date table? Can you share a sample of your Date table here? I want to know the columns you have in that table to provide the better solution.

Appreciate your Kudos!!
LinkedIn:
www.linkedin.com/in/vahid-dm/

Helper III

I attached also the slicers that are in my model:

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors