topic Re: Average of beginning and ending total assets in DAX Commands and Tips

Average of beginning and ending total assets

rwong1 — Mon, 29 Nov 2021 20:07:31 GMT

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

Re: Average of beginning and ending total assets

VahidDM — Mon, 29 Nov 2021 22:32:34 GMT

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/

Re: Average of beginning and ending total assets

rwong1 — Mon, 29 Nov 2021 22:43:25 GMT

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.

Re: Average of beginning and ending total assets

rwong1 — Mon, 29 Nov 2021 22:58:19 GMT

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.

Re: Average of beginning and ending total assets

VahidDM — Tue, 30 Nov 2021 01:14:46 GMT

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/

Re: Average of beginning and ending total assets

rwong1 — Tue, 30 Nov 2021 02:59:13 GMT

Hi,

Yes I do. Please see below a screenshot:

Re: Average of beginning and ending total assets

rwong1 — Tue, 30 Nov 2021 03:24:42 GMT

I attached also the slicers that are in my model:

Re: Average of beginning and ending total assets

VahidDM — Tue, 30 Nov 2021 04:20:25 GMT

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/

Re: Average of beginning and ending total assets

rwong1 — Tue, 30 Nov 2021 04:39:48 GMT

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]

Re: Average of beginning and ending total assets

VahidDM — Tue, 30 Nov 2021 05:13:50 GMT

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/

Re: Average of beginning and ending total assets

rwong1 — Tue, 30 Nov 2021 15:00:39 GMT

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.

Re: Average of beginning and ending total assets

rwong1 — Wed, 01 Dec 2021 02:46:06 GMT

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