PowerBI DAX formula to calculate average of month for prior 2 year

1
votes

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?

1
powerbidaxcalculated-columns

1 Answers

1
votes

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