topic Re: Rolling Sum that Uses a Variable Factor in DAX Commands and Tips

Rolling Sum that Uses a Variable Factor

Brotedo — Fri, 26 Aug 2022 15:34:17 GMT

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.

Re: Rolling Sum that Uses a Variable Factor

tamerj1 — Fri, 26 Aug 2022 19:31:29 GMT

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] ) )

Re: Rolling Sum that Uses a Variable Factor

v-chenwuz-msft — Wed, 31 Aug 2022 08:35:43 GMT

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.