How to calculate Year on Year (YoY) Growth?

atin · May 11, 2020, 8:01pm

Hi all - based on spreadsheet below. I have the Sales data for 2019 & 2020
However I do not have the 2021 sales data.

I intend to calculate an estimate based on the prior year.
I calculated the following measures:
Sales LY
Sales TY vs LY
YoY Sales Growth

2021 Sales Budget (Column E4) should be a calculation of the YoY growth rate multiplied by the Sales LY
My question is how do I create a measure for this.

An option is to hardcode the number eg Sales Budget = [Sales LY] * 1.44
However I do not want to use hard numbers in my calculations

I also tried this measure:
Sales Budget = [Sales LY] * ( 1 + [YoY Sales Growth] ) and this did not work. The YoY sales growth came up with 0.0

Please your suggestions would be welcome

Many thanks

C9411010 · May 11, 2020, 11:44pm

Does this work for you

Sales FY = 
    var SalesCurrentYear = Calculate(sum(SalesData[SalesValue]), FILTER(SalesData, SalesData[Year] = Year(Today()) ))
    var SalesPreviousYear = Calculate(sum(SalesData[SalesValue]), FILTER(SalesData, SalesData[Year] = Year(Today()) - 1 ))
    var YOYGrowth = 1 + DIVIDE(SalesCurrentYear - SalesPreviousYear, SalesPreviousYear)
    return 
        SalesCurrentYear * YOYGrowth

atin · May 12, 2020, 2:53am

Many thanks for getting back. The Final numbers I am looking for should be [Sales LY] * 1.44 which is 28,182,610 * 1.44 = 40,582,958 . I am calculating the estimated sales for 2021 based on a growth rate of 44%

I have some questions on your measure.

Within the Sales Table, I do not have a Sales Value. One of the key measures I created was for Total Sales

My question is on the:

Calculate(sum(SalesData[SalesValue]) - Is this Measure one that you created or is the Sales Value number part of the dataset
SalesData[Year] - is this part of the original data set as I have created a Dates Table in my reports and using this. Would I have to use Dates[Year] for this.
Year (Today () Not sure what this is? Is this similar to an excel function where =Today() gives you today’s date and does the Year fall within the Dates Table

This is what I have been able to create so far based on your example: After sum of Sales, the [TotalSales) measure created is in a “Key Measure” Group - I don’t have a Sales Value within my original dataset. I created a Total Sales measure

C9411010 · May 12, 2020, 3:10am

This change will calculate the sales over the previous year

Sales FY

var SalesCurrentYear = Calculate(sum(SalesData[SalesValue]), FILTER(SalesData, SalesData[Year] = Year(Today()) ))
var SalesPreviousYear = Calculate(sum(SalesData[SalesValue]), FILTER(SalesData, SalesData[Year] = Year(Today()) - 1 ))
var YOYGrowth = 1 + DIVIDE(SalesCurrentYear - SalesPreviousYear, SalesPreviousYear)
return 
    SalesPreviousYear * YOYGrowth

#1 It is a measure that i created to capture total sales for all filter context. In your table, I see you have quantity but do you have price also ? Is the price stored in the Sales table or is it part of the products table. If it is the sales table then you can write a measure
[Total Sales] = sumx(Sales, Sales[Quantity] * Sales[Price]
if price is part of product table
[Total Sales] = sumx(Sales, Sales[Quantity] * Related(Product[Price)]

#2 i am using the Year(Today()) to get the current year. You are right , it is similar to the excel formula Today(), which returns today date and the year function returns current year 2020
to get previous year , I used Year(Today()) - 1and for future year Year(Today()) + 1. You will have to use Dates[Year] in your code as long as you have the 1 to Many relationship defined between your date table and the sales table.

#3 i covered as part of #2

feel free to share your pbix file, if you run into issues.

atin · May 12, 2020, 3:13am

Hi @C9411010 - Please see further information on other measures I had calculated:

Measure SalesLY :

Sales LY = CALCULATE( [Total Sales] , DATEADD( Dates[Date] , -1, YEAR ) )

Measure YoY Growth Rate:
YoY Sales Growth New = DIVIDE( [Sales TY vs LY ] , [Sales LY] , 0 )

Sales This Year vs Last Year:
Sales TY vs LY = IF( ISBLANK( [Sales LY] ) , BLANK() , [Total Sales] - [Sales LY] )

Total Sales:
Total Sales = SUMX(Sales , Sales[Quantity] * RELATED( ‘Product’[Current Price] ) )

C9411010 · May 12, 2020, 3:15am

This looks great. Did it work ok for you?

atin · May 12, 2020, 3:16am

Many thanks for getting back.

I did not see your reply before sending my message.

Will review your responses

atin · May 12, 2020, 4:07am

I have created the measure based on your example - see details below:

Estimated Sales =
VAR SalesCurrentYear = CALCULATE( [Total Sales], FILTER( Dates, Dates[Year] = YEAR( TODAY() ) ) )
VAR SalesPreviousYear = CALCULATE( [Total Sales] , FILTER( Dates, Dates[Year] = YEAR( TODAY() ) -1 ) )
VAR YoYGrowth = 1 + DIVIDE( SalesCurrentYear - SalesPreviousYear, SalesPreviousYear )
RETURN
SalesCurrentYear * YoYGrowth

However I got an error message when creating a visual:

See error message below:

C9411010 · May 12, 2020, 4:11am

Can you try wrapping year(Today())-1 in the value function like this Value(Year(Today())) -1

atin · May 12, 2020, 4:34am

I have wrapped the Year using the VALUE function and unfortunately getting the same error.

Please see details below

C9411010 · May 12, 2020, 11:20pm

Hello Atin - Not sure why you were getting the error. i built a sample pbix file . find attached.
SalesCYPYFY.pbix (125.4 KB)

atin · May 14, 2020, 1:54am

Hi @C9411010

Many thanks for forwarding your file.
I have seen your calculations however the results that I am looking for should be 40.6M which is the estimated Sales budget for 2019 ( based on a YoY% growth of 44% and Sales LY being 28.2M

Please find attached my pbix file. with 2 pages:

First page - with hard number in the calculations

Sales Budget Estimate = [Sales LY] * 1.44 which gives a result of 40.6M in 2019

2nd page - No Hard numbers in calculation

Sales Budget TEST = [Sales LY] * ( 1 + [YoY Sales Growth] ) Nil Results in 2019

SalesBudgetCalc.pbix (536.8 KB)

C9411010 · May 14, 2020, 3:50am

Hello Atin - i simplified your visual a little bit. find pbix attached . you can select any year to do the analysis

in the attached image you have a selected year e.g. Year 2018. the sales LY is the sales in the Year 2017, the YOY sales growth is the diff in sales over the sales in year 2017
sales FY i.e sale in 2019 is the growth rate * Year 2018 sales

Note that sales FY is the projected sale[SalesBudgetCalc.pbix|attachment]SalesBudgetCalc.pbix (534.7 KB)

atin · May 15, 2020, 4:37am

Hi @C9411010

I will review this and get back to you

Many thanks for this

atin · May 16, 2020, 6:23am

Hi @C9411010

The formulae works. Many thanks.

When selecting the Year 2018, I am able to view the Sales FY of 40.7M

However when I select 2019, then the Sales FY is blank and not able to view the 40.7M. This option only appears to work when a hard number is used for the YoY growth (ie [Sales LY] * 1.44)

Thanks for all your help.

C9411010 · May 17, 2020, 1:50pm

Atin - that is because the data provided had no actual sales for the year 2019.