I want to calculate the estimated returns for current month, based on the average returns for the same month in the last 2 years. Eg:
Sales
Date Sales Returns
10/21/2018 500
10/20/2018 120
10/05/2017 546 254
10/10/2017 185 90
10/17/2016 255 120
10/8/2016 153 20
Aggregated values:
Sales
Month Sales Returns Returns To Sales Ratio
Oct-18 620
Oct-17 731 344 0.4705
Oct-16 408 140 0.3431
Average Oct returns for last 2 years : 0.4068 (0.4705+0.3431 / 2). I am talking the avg of % and not summing the 2 years and taking %.
Sales
Month Sales Expected Returns
Oct-18 620 252.21 (620*0.4068)
How can I create a measure which would give me an expected return for any month by taking the average returns for that month of last 2 years and multiplying by sale of that month.
To make things a little simple I created another table to get me the cumulative sales and returns information.
Table1
Month Year Sales Returns
Oct 2018 620
Oct 2017 731 344
Oct 2016 408 140
Sept 2018 525 256
Sept 2017 215 154
Sept 2016 145 115
April 2018 531 258
April 2017 815 486
April 2016 158 78
Then I created a calculated column
Returns % = 'Table1'[Returns]/'Table1'[Sales]
Can I create a calculated columns to show me the Last 2 year avg? If not, How do I create a measure?
I Have used the Aggregated table to find the solution
Sales
Month Sales Returns Returns To Sales Ratio
Oct-18 620
Oct-17 731 344 0.4705
Oct-16 408 140 0.3431
I have used following calculated column for expected returns
***Expected Returns = var prevyrs = LOOKUPVALUE('Sales'[Returns to Sales Ratio],'Sales'[Month],DATEADD('Sales'[Month],-1,YEAR))
var lastyrs = LOOKUPVALUE('Sales'[Returns to Sales Ratio],'Sales'[Month],DATEADD('Sales'[Month],-2,YEAR))
var avgret = (prevyrs+lastyrs)/2
return IF(ISBLANK('Sales'[Returns]),avgret*'Sales'[Sales],'Sales'[Returns])***
Link of Screenshot
