3 Month Inventory Average

[chenko90]

Hi,

I am trying to figure out a measure i could write in powerbi that I could use to calculate a 3 month running average for my inventory. I have it broken out by month and year and i just need to be able to find say Jan 2018, Feb 2018, March 2018 average; then Feb 2018, March 2018, April 2018 average...ect 

[Anonymous · Accepted solution]

Hi,

I recreated that exact data set in an excel workbook and plugged it into PowerBI. The first thing I did was actually create a Date-based column:

DATE = DATE(Sheet1[Year],Sheet1[Period],1)

Then, I used the following formula to create a calculated measure:

3 Month Avg = CALCULATE(SUM(Sheet1[Inventory]),DATESINPERIOD(Sheet1[DATE],LASTDATE(Sheet1[DATE]),-3,MONTH))/3

I think this gives what you're looking for, although you can see below that it's not working properly for the first two months (11/17 and 12/17). This is because diving a sum / 3 for a time period that doesn't have 3 full months of data. You could make this more dynamic than the method of manually dividing by 3 as I did above if that poses a challenge.

Ben

[chenko90]

Thanks Ben, that worked!