Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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 , 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.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |