cancel
Showing results 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

Frequent Visitor

## Calculated Columns using one year average and not calculating if prior year is missing

I have over 30,000 observations of annual financial data dating from 2010 to 2020 that I want to use with my students.  I have a very short example below.  We calculate Return on Equity (ROE) as Net Income divided by average Equity where the average is computed as the prior year ending plus current year ending divided by two. I believe it is easiest with two calculated columns:

1 - Calculate Average Equity by taking the current equity plus the previous fiscal year divided by two.  If there is no previous fiscal year shown, then this data should be null or missing.  (note that each company has different years as some companies started in 2015, others went out of business in 2012, etc.)

2 - Calculate ROE by taking Net Income divided by the column I just calculated in part 1.  However, if Net income is less than 0 or if Average equity is less than zero, then ROE should be null or missing

I want ROE as a calculated column as I want to look at ROE for the industry, etc., and I don't want negative net income or negative equity items included in the analysis numbers.

Here is a small sample of the data.

The Global Company key identifies the company - for example, 1690 is Apple Inc.

The Fiscal Year is the year I'm looking at (and which will differ from company to company)

The final two columns are what I want to create with the missing or null items signified with "(null)"

 Global Company Key Fiscal Year Equity Net Income (Loss) TO CREATE: Average Equity TO CREATE: ROE 1690 12/31/2010 \$                    47,791 \$                       14,013 (null) (null) 1690 12/31/2011 \$                    76,615 \$                       25,922 \$                          124,406 20.84% 1690 12/31/2012 \$                  118,210 \$                       41,733 \$                          194,825 21.42% 1690 12/31/2013 \$                  123,549 \$                       37,037 \$                          241,759 15.32% 1690 12/31/2014 \$                  111,547 \$                       39,510 \$                          235,096 16.81% 1690 12/31/2015 \$                  119,355 \$                       53,394 \$                          230,902 23.12% 1690 12/31/2016 \$                  128,249 \$                       45,687 \$                          247,604 18.45% 1690 12/31/2017 \$                  134,047 \$                       48,351 \$                          262,296 18.43% 1690 12/31/2018 \$                  107,147 \$                       59,531 \$                          241,194 24.68% 1690 12/31/2019 \$                    90,488 \$                       55,256 \$                          197,635 27.96% 027638 12/31/2014 \$                    10,599 \$                          (256) (null) (null) 027638 12/31/2015 \$                       9,442 \$                          (863) \$                            20,041 (null) 027638 12/31/2016 \$                       5,654 \$                          (400) \$                            15,096 (null) 027638 12/31/2017 \$                       4,523 \$                             217 \$                            10,177 2.13% 027638 12/31/2018 \$                       5,389 \$                             227 \$                               9,912 2.29% 027638 12/31/2019 \$                       4,112 \$                       (1,125) \$                               9,501 (null) 001045 12/31/2010 \$                    (3,945) \$                          (471) (null) (null) 001045 12/31/2011 \$                    (7,111) \$                       (1,979) \$                          (11,056) (null) 001045 12/31/2012 \$                    (7,987) \$                       (1,876) \$                          (15,098) (null) 001045 12/31/2013 \$                    (2,731) \$                       (1,834) \$                          (10,718) (null) 001045 12/31/2014 \$                       2,021 \$                         2,882 \$                                (710) (null) 001045 12/31/2015 \$                       5,635 \$                         7,610 \$                               7,656 99.40% 001045 12/31/2016 \$                       3,785 \$                         2,676 \$                               9,420 28.41% 001045 12/31/2017 \$                       3,926 \$                         1,919 \$                               7,711 24.89% 001045 12/31/2018 \$                      (169) \$                         1,412 \$                               3,757 37.58% 001045 12/31/2019 \$                      (118) \$                         1,686 \$                                (287) (null)
1 ACCEPTED SOLUTION
Super User

You're not dividing by 2 for the average equity in the sample data shown. Add the division by 2 if necessary here:

``````Avg Equity =
VAR previousYear_ =
CALCULATE (
MAX ( Table1[Fiscal Year] ),
Table1[Fiscal Year] < EARLIER ( Table1[Fiscal Year] ),
ALLEXCEPT ( Table1, Table1[Global Company Key] )
)
RETURN
IF (
NOT ISBLANK ( previousYear_ ),
Table1[Equity]
+ CALCULATE (
SUM ( Table1[Equity] ),
Table1[Fiscal Year] = previousYear_,
ALLEXCEPT ( Table1, Table1[Global Company Key] )
)
)``````

``````ROE =
IF (
Table1[Avg Equity] > 0,
DIVIDE ( Table1[Net Income (Loss)], Table1[Avg Equity] )
)
``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

2 REPLIES 2
Frequent Visitor

Thanks - that worked great!  I did forget to divide by 2 and added that to the code and everything works great.  Thanks so much!
(Note that I can now show my students the difference in overall ROE if they do it as a calculated column versus as a measure and how they need to think through this as you get REALLY different overall results with each one).

Super User

You're not dividing by 2 for the average equity in the sample data shown. Add the division by 2 if necessary here:

``````Avg Equity =
VAR previousYear_ =
CALCULATE (
MAX ( Table1[Fiscal Year] ),
Table1[Fiscal Year] < EARLIER ( Table1[Fiscal Year] ),
ALLEXCEPT ( Table1, Table1[Global Company Key] )
)
RETURN
IF (
NOT ISBLANK ( previousYear_ ),
Table1[Equity]
+ CALCULATE (
SUM ( Table1[Equity] ),
Table1[Fiscal Year] = previousYear_,
ALLEXCEPT ( Table1, Table1[Global Company Key] )
)
)``````

``````ROE =
IF (
Table1[Avg Equity] > 0,
DIVIDE ( Table1[Net Income (Loss)], Table1[Avg Equity] )
)
``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

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.