SUM of Next 3 Months' Average

DAX DAX Calculations
1

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.
Basic Model Forecast Demand.pbix (1.0 MB)

1 Like
2

Hi @npeterson,

Can you state your requirement more clearly and provide the expexted result?

Your report is filtered on Jan-Feb-Mar only. First, what do you consider to be the “next 3 months”? Should the Sum of Avg for Jan be => Feb + Mar + Apr? or Jan + Feb + Mar next year? or something else … ?

1 Like
3

The goal for this measure is to have a value to compare against quantity of stock on hand. We want to make sure we have at least a three-month supply available. 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. Thanks for your assistance @Melissa!

1 Like
4

Hi @npeterson,

Wonderful, thanks! Here’s what I’ve done.

Added a MonthID column to your Dates table, that generates a sequential number that increases for each month and makes these types of requirements childs play.

image

.
Next created this measure to go along with it

Sum next 3 Months Avg =
VAR _Month = SELECTEDVALUE( Dates[MonthID], MAX(Dates[MonthID]))
VAR _Num = 3
VAR _tMonth =
    ADDCOLUMNS(
        FILTER( ALL( Dates[MonthID] ),
            Dates[MonthID] > _Month &&
            Dates[MonthID] <= _Month + _Num
        ),
        "@Avg", [Avg Usage]
    )
RETURN

SUMX( _tMonth, [@Avg] )

.
This looks up the current MonthID within the filter context.
Has a parameter to set the number of future months to consider
Creates a table with those future months, leverages your [Avg Usage] measure to calculate a value for each month and finally sums them up.

Currently your [Avg Usage] seems to be an all time Avg but you can easily replace that measure with any another.

I hope this is helpful

1 Like
5

Thank you @Melissa. I appreciate the explanation to go along with the formula. That makes sense.

1 Like