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
Anonymous
Not applicable

calculate NYSE revenue growth %

I am trying to calculate the revenue growth for stocks listed on the New York Stock Exchange but I'm having difficulty filtering the previous year it should pick as each stock listed has record for 4 years. This is a sample of the data.

I want to calculate revenue growth such that when I filter each stock, I'll get the revenue growth for each year.

 

TickerSymbolYearsPeriodEnding TotalRevenue 
AALYear112/31/2012 $     24,855,000,000.00
AALYear212/31/2013 $     26,743,000,000.00
AALYear312/31/2014 $     42,650,000,000.00
AALYear412/31/2015 $     40,990,000,000.00
AAPYear112/29/2012 $       6,205,003,000.00
AAPYear212/28/2013 $       6,493,814,000.00
AAPYear31/3/2015 $       9,843,861,000.00
AAPYear41/2/2016 $       9,737,018,000.00

 

The Dax formular used I attempted is below:

REVENUE GROWTH % =
VAR CURRYEAR = CALCULATE(SUM('projectdata-nyse'[TotalRevenue]),DATEADD('projectdata-nyse'[PeriodEnding],1,YEAR))
VAR PRVYEAR= CALCULATE(SUM('projectdata-nyse'[TotalRevenue]),DATEADD('projectdata-nyse'[PeriodEnding],-1,YEAR))
VAR z = CURRYEAR - PRVYEAR

RETURN
DIVIDE(z,PRVYEAR)
1 ACCEPTED SOLUTION
SteveHailey
Solution Specialist
Solution Specialist

A proper date table is required to use DATEADD (see: DATEADD – DAX Guide).

 

Without a date table, I would do something like this:

 

Revenue Growth % = 
VAR CY = YEAR( MAX( 'projectdata-nyse'[PeriodEnding] ) )
VAR PY = CY - 1
VAR Ticker = MAX( 'projectdata-nyse'[TickerSymbol] )
VAR CYRevenue =
    CALCULATE(
        SUM( 'projectdata-nyse'[ TotalRevenue ] ),
        FILTER(
            ALL( 'projectdata-nyse' ),
            YEAR( 'projectdata-nyse'[PeriodEnding] ) = CY
                && 'projectdata-nyse'[TickerSymbol] = Ticker
        )
    )
VAR PYRevenue =
    CALCULATE(
        SUM( 'projectdata-nyse'[ TotalRevenue ] ),
        FILTER(
            ALL( 'projectdata-nyse' ),
            YEAR( 'projectdata-nyse'[PeriodEnding] ) = PY
                && 'projectdata-nyse'[TickerSymbol] = Ticker
        )
    )
VAR RevenueGrowth = DIVIDE( CYRevenue, PYRevenue ) - 1
VAR Result =
    IF(
        NOT ( ISBLANK( PYRevenue ) ) && HASONEVALUE( 'projectdata-nyse'[PeriodEnding] ),
        RevenueGrowth
    )
RETURN
Result

 

SteveHailey_0-1641083666221.png

 

Here's a link to a .pbix file, if it helps.

 

-Steve

View solution in original post

2 REPLIES 2
SteveHailey
Solution Specialist
Solution Specialist

A proper date table is required to use DATEADD (see: DATEADD – DAX Guide).

 

Without a date table, I would do something like this:

 

Revenue Growth % = 
VAR CY = YEAR( MAX( 'projectdata-nyse'[PeriodEnding] ) )
VAR PY = CY - 1
VAR Ticker = MAX( 'projectdata-nyse'[TickerSymbol] )
VAR CYRevenue =
    CALCULATE(
        SUM( 'projectdata-nyse'[ TotalRevenue ] ),
        FILTER(
            ALL( 'projectdata-nyse' ),
            YEAR( 'projectdata-nyse'[PeriodEnding] ) = CY
                && 'projectdata-nyse'[TickerSymbol] = Ticker
        )
    )
VAR PYRevenue =
    CALCULATE(
        SUM( 'projectdata-nyse'[ TotalRevenue ] ),
        FILTER(
            ALL( 'projectdata-nyse' ),
            YEAR( 'projectdata-nyse'[PeriodEnding] ) = PY
                && 'projectdata-nyse'[TickerSymbol] = Ticker
        )
    )
VAR RevenueGrowth = DIVIDE( CYRevenue, PYRevenue ) - 1
VAR Result =
    IF(
        NOT ( ISBLANK( PYRevenue ) ) && HASONEVALUE( 'projectdata-nyse'[PeriodEnding] ),
        RevenueGrowth
    )
RETURN
Result

 

SteveHailey_0-1641083666221.png

 

Here's a link to a .pbix file, if it helps.

 

-Steve

Anonymous
Not applicable

Thank you for the .pbix file. The solution worked.

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.

Top Solution Authors