# Power BI Exchange

View Only

## SUM of 3 Month Average

• #### 1.  SUM of 3 Month Average

Silver Contributor
Posted 24 days ago

I'm working on a model that will flag items that may fall below expected demand. In order to do this, I need to calculate the average monthly usage for each month (January average, February average, etc.). Then create a measure that will SUM the monthly averages for the next 3 months. Please help me accomplish this in DAX.

------------------------------
Nancy Peterson
Pitsco Education
Pittsburg KS
------------------------------

Attachment(s)

• #### 2.  RE: SUM of 3 Month Average

Posted 23 days ago
Morning Nancy,

In your calendar DIM create 2 columns. One with RANKX, will give you index to your months and then next column I call it MONTH RELATED as Rank in a row minus current month Rank. At the end you should land with column, when ALWAYS current month will be 0, last month, -1, previous -2 , etc. And following month will 1, 2 months ahead 2 and so on. Than you can write mesure to calculate average for all months when rank is less than 3 from month you are calculate average.

I hope it will help.

Regards,

------------------------------
------------------------------

• #### 3.  RE: SUM of 3 Month Average

Silver Contributor
Posted 23 days ago
Adding the offset to the Dates table will simplify the filter criteria, but I'm struggling to get a measure that will give me a SUM of the average.

------------------------------
Nancy Peterson
Pitsco Education
Pittsburg KS
------------------------------

• #### 4.  RE: SUM of 3 Month Average

Posted 22 days ago
Olá
Veja se o anexo ajuda

------------------------------
Vilmar Santos
------------------------------

Attachment(s)

• #### 5.  RE: SUM of 3 Month Average

Silver Contributor
Posted 22 days ago
The measure you created @Vilmar Santos does provide the sum of the average, but I need for it to always give me the sum of the next 3 months. So in November, the measure would find the overall averages of December, January, and February​​ and then add these three numbers together. Regardless of the filters on the page, the measure would always show the sum of the average for the next 3 months. This allows the manufacturing team to look at the quantity on hand and determine if we have enough on hand to make it through the next 3 months.

------------------------------
Nancy Peterson
Pitsco Education
Pittsburg KS
------------------------------

• #### 6.  RE: SUM of 3 Month Average

Bronze Contributor
Posted 21 days ago
Hi Nancy,

Does this give you the desired result? For convenience, I added a new month column in the date table that also contains the year, then I changed the measure "Avg Usage" so that it is just a regular average connected to the date table trough delivery date. Finally, I added this measure:

``````Sum of Average Usage Next Three Months =
VAR SelectedMonth =
SELECTEDVALUE ( Dates[Month] )
VAR LastMonthInPeriod =
DATE ( YEAR ( SelectedMonth ), MONTH ( SelectedMonth ) + 3, 1 )
VAR Result =
CALCULATE (
SUMX ( VALUES ( Dates[Month] ), [Avg Usage] ),
REMOVEFILTERS ( Dates ),
Dates[Month] > SelectedMonth,
Dates[Month] <= LastMonthInPeriod
)
RETURN
Result​``````
Please note that the way it is currently defined, it only gives a result when a single month/year-combination is visible in the filter context.

------------------------------
Tomas
------------------------------

Attachment(s)