Calculating Sales Year end estimate (YEE) based on Last year total

[martinkoe]

Hello,

i was not succesful with my recherche regarding this and having studied the function and possibilities of PowerBI i am wondering that this hasnt been a standard function yet.

Having all Sales data from invoices (Date / Customer / Sales item ID / Amount ...  / Sales value) since a decade as well as build a YTD from that i would like to have a single value for the YEE total. A linear FC does not make much sense, therefore i would like to consider the past year seasonal characteristic. Something like:

(Sales YTD - Sales PYTD) / Sales PYTD x (total Sales last year) 

Would anyone by so kind and give me a hint into the right direction?

/thx M

[martinkoe · Accepted solution]

Hello folks,

finally found a way to integrate the last year seasonal characteristic and measuring it against YTD performance.

// Sales growth rate ... calculates your YTD performance versus PYTD (nothing special so far)

Sales growth rate = iferror(([Sales YTD]- [Sales PYTD])/[Sales PYTD],blank())

// Prior Year  ... returns your last year to date total, but fills your column till your current year end (the golden nugget i was looking for) 

Sales PYE = CALCULATE(sum(invoices[Sales Value]),DATESYTD(DATEADD(Datum[Date],-1,YEAR),"31.12"))

// Year end estimate

Sales YEE = CALCULATE( [Sales PYE], ENDOFYEAR(Datum[Date],"31.12"))*(1+[Sales growth rate])

Recommendation: use either a line graph to see if your potential moved up/down over the year or a KPI (Date = today) to show the status of the prediction. In the first quarter pretty vague, but gets pretty accurate from 2nd Quarter on.  

It is very simple but stronger than a linear prediction or math algorithm as it considers your seasonal business. 

eg.: If your last Quarter is typically very strong/weak, the alorithm would not cover that change. 

Let me know how it works on your side.

//M

[martinkoe]

Tried it this way, unfortunately doesn't work, das Sales growth rate is not accepted in LASTNONBLANK()

Last SGR = if (ISBLANK([Sales growth rate]),LASTNONBLANK([Sales growth rate]),[Sales growth rate])

🤔

[amitchandak]

@martinkoe , based on what I got so far. You can try measures like with help from time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date])) //complete

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[martinkoe]

Hi @amitchandak ,

while 

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))  provides now a value for 31.12.2021 of the last year total sales i still need the growth YTD to push the current value into 31.12.2021.

So ... my table for growth rate ends with today. The value is eg. 15%, so i would like to get to YEE = Sales growth rate x Last YTD Sales

Sales growth rate = ([Sales YTD]- [Sales PYTD])/[Sales PYTD]

Problem: currently Sales growth rate (22.07.2021) is multiplied with Last YTD Sales (22.07.2021)

So is there a simple way to continue the current value for Sales growth rate till end of the year? 

/thx M