Rolling Sum that Uses a Variable Factor

[Brotedo]

I have a monthly sales dataset, like this:

MonthIndex	Sales
1	45
2	55
3	45
4	40
5	50
6	40
7	70
8	60
9	50
10	55
11	50
12	40

And I have a table of factors, like this:

MonthsFromCurrent	Factor
0	.7
1	.5
2	.2
3	.06

I need a measure that, for the given month, calculates the sum of the products of the sales and factors. For example, if the current month is 6, I want the measure to output (40*.7 + 70*.5 + 60*.2 + 50*.06) = 78 

The factor for all other months is 0.

[v-chenwuz-msft · Accepted solution]

Hi @Brotedo ,

Maybe you can try this.

Measure =
VAR _currentmonth =
    SELECTEDVALUE ( Parameter[Parameter] )
VAR _f =
    ADDCOLUMNS (
        'factors',
        "sales1",
            CALCULATE (
                SUM ( sales[Sales] ),
                FILTER (
                    'sales',
                    [MonthIndex]
                        = EARLIER ( [MonthsFromCurrent] ) + _currentmonth
                )
            ) * [Factor]
    )
RETURN
    SUMX ( _f, [sales1] )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Brotedo 

please try

NewMeasure =
SUMX (
    FILTER (
        ALLSELECTED ( Sales ),
        Sales[MonthIndex] >= MAX ( Sales[MonthIndex] )
            && Sales[MonthIndex]
                < MAX ( Sales[MonthIndex] ) + COUNTROWS ( Factors )
    ),
    SUMX (
        FILTER ( Factors, Factors[MonthsFromCurrent] = Sales[MonthIndex] ),
        Sales[Sales] * Factors[Factor]
    )
)