Average of beginning and ending total assets

[rwong1]

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))

[rwong1]

Hi,

Yes I do.  Please see below a screenshot:

[VahidDM]

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/

[VahidDM · Accepted solution]

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/

[rwong1]

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

[rwong1]

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]

[VahidDM]

@rwong1 

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/

[rwong1]

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.

[rwong1]

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.

[rwong1]

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.  

[VahidDM]

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/

[rwong1]

I attached also the slicers that are in my model: