Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all, I need your help in calculating the forward forecast for sales. I have monthly sales data.
Here is the equation for forecast calculation,
Forecast = SalesLY * CIValue * rValue
SalesLY = Last year same month sales
CIValue = we have this column in the growth projection table
rValue = sum of current year 3 months/sum of last year 3 months
Let's say we are in Aug2019, the sum of August 2020, July 2020 & June 2020 divided by the sum of August 2019, July 2019 & June 2019.
We have to calculate the forecast for a year from the current month. Let's say we are in August 2020 then it should calculate forecast till August 2021.
Here is the file with the sample data Forecast Test.
Thanks
Solved! Go to Solution.
Hi, @Gaurav_Lakhotia ;
You mentioned that sum of current year 3 months is sum of sales? if so , you could create a measure as follows:
1.create SalesLY measure.
SaleLY = CALCULATE(SUM('Sales'[Sales]),FILTER(ALL('Sales'),[Year]+1=MAX('Growth Projection'[Year])&&[Month]=MAX('Growth Projection'[Month])))
2.create rValue measure.
rValue =
VAR _currentq =
CALCULATE (
SUM ( 'Sales'[Sales] ),
FILTER (
ALL ( 'Sales' ),
[Year] = MAX ( 'Growth Projection'[Year] )
&& QUARTER ( [Date] ) = QUARTER ( MAX ( 'Growth Projection'[date] ) ) ))
VAR _lastq =
CALCULATE (
SUM ( 'Sales'[Sales] ),
FILTER (
ALL ( 'Sales' ),
[Year] + 1
= MAX ( 'Growth Projection'[Year] )
&& QUARTER ( [Date] ) = QUARTER ( MAX ( 'Growth Projection'[date] ) )))
RETURN DIVIDE ( _currentq, _lastq )
3.create Forecast measure.
Forecast = [SaleLY]*SUM([CIValue])*[rValue]
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Gaurav_Lakhotia ;
You mentioned that sum of current year 3 months is sum of sales? if so , you could create a measure as follows:
1.create SalesLY measure.
SaleLY = CALCULATE(SUM('Sales'[Sales]),FILTER(ALL('Sales'),[Year]+1=MAX('Growth Projection'[Year])&&[Month]=MAX('Growth Projection'[Month])))
2.create rValue measure.
rValue =
VAR _currentq =
CALCULATE (
SUM ( 'Sales'[Sales] ),
FILTER (
ALL ( 'Sales' ),
[Year] = MAX ( 'Growth Projection'[Year] )
&& QUARTER ( [Date] ) = QUARTER ( MAX ( 'Growth Projection'[date] ) ) ))
VAR _lastq =
CALCULATE (
SUM ( 'Sales'[Sales] ),
FILTER (
ALL ( 'Sales' ),
[Year] + 1
= MAX ( 'Growth Projection'[Year] )
&& QUARTER ( [Date] ) = QUARTER ( MAX ( 'Growth Projection'[date] ) )))
RETURN DIVIDE ( _currentq, _lastq )
3.create Forecast measure.
Forecast = [SaleLY]*SUM([CIValue])*[rValue]
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |