Latest Enterprise DNA Initiatives


How to calculate Year on Year (YoY) Growth?

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

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

image

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:

  1. Calculate(sum(SalesData[SalesValue]) - Is this Measure one that you created or is the Sales Value number part of the dataset

  2. 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.

  3. 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

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.

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

This looks great. Did it work ok for you?

Many thanks for getting back.

I did not see your reply before sending my message.

Will review your responses

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:

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

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

Please see details below

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

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)

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)

Hi @C9411010

I will review this and get back to you

Many thanks for this

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.

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