Rolling Sum with SUMX not working as row-wise

luisccmm — Thu, 19 Oct 2023 09:22:11 GMT

I have created a Measure that is working correctly but I am failling to create Rolling Sum from it.

The following is the original measure. At the end, after "Return" there are 2 main parts on the multiplication expression,
Part1) there is a SUMX function that calculates a ratio for each Yearmonth and then multiplies by
Part2) sum of ([Active_contracts]+[Future_contracts])

Till here there is no problem.

Month_value_measure= var _fin=max(Calendario_forecast[Fecha]) var _days=max(Forecast_periodo[Forecast_periodo]) var _start=max(Calendario_forecast[Fecha])-_days var _maxyear=max('Master Calendar'[Year]) var _laborables= CALCULATE(sum('Master Calendar'[Working_day_flag]), FILTER(all('Master Calendar'[Fecha]),'Master Calendar'[Fecha]>=_start&&'Master Calendar'[Fecha]<=_fin)) return SUMX ( VALUES ( 'Master Calendar'[Year_month_num] ), DIVIDE ( CALCULATE ( DIVIDE ( CALCULATE ( DIVIDE ( [Churned_contracts], [Active_Contracts] ), FILTER ( ALL ( 'Master Calendar'[Date] ), 'Master Calendar'[Date] >= _start && 'Master Calendar'[Date] <= _fin ) ), _working_days ) * [Working_days_acumulative], FILTER ( 'Master Calendar', 'Master Calendar'[Date] >= _fin )* ([Active_contracts]+[Future_contracts])

Results
Results from above measure are used with Master Calendar Yearmonth colum, producing one value for each month as follows.

Year_month_num	Month_value_measure
202310	120
202311	200
202312	300

What I am looking for? Calculating Rolling SUM for each value of Month_value_measure (remember that results are aggregated by Yearmonth_num), producing something like the third column from the following table.

Year_month_num	Month_value_measure	Rolling_sum_measure
202310	120	120
202311	200	320
202312	300	620

My Attempt Problem.

THe problem comes when I try to calculate a Rolling SUM from this last measure, as I need to calculate it row-wise and then sum the total from each row. What it is actually doing (not expected ) is summing all values from Part1) and multiply them by the sum of all values from Part2)

The following is not working

Rolling_sum_measure= var _fin=max(Calendario_forecast[Fecha]) var _days=max(Forecast_periodo[Forecast_periodo]) var _start=max(Calendario_forecast[Fecha])-_days var _maxyear=max('Master Calendar'[Year]) var _laborables= CALCULATE(sum('Master Calendar'[Working_day_flag]), FILTER(all('Master Calendar'[Fecha]),'Master Calendar'[Fecha]>=_start&&'Master Calendar'[Fecha]<=_fin)) return SUMX ( VALUES ( 'Master Calendar'[Year_month_num] ), DIVIDE ( CALCULATE ( DIVIDE ( CALCULATE ( DIVIDE ( [Churned_contracts], [Active_Contracts] ), FILTER ( ALL ( 'Master Calendar'[Date] ), 'Master Calendar'[Date] >= _start && 'Master Calendar'[Date] <= _fin ) ), _working_days ) * [Working_days_acumulative], FILTER ( 'Master Calendar', 'Master Calendar'[Date] >= _fin )* ([Active_contracts]+[Future_contracts]), FILTER ( ALL ( 'Master Calendar'[Date] ), 'Master Calendar'[Date] <= MAX ( 'Master Calendar'[Date] ) ), 'Master Calendar'[Year] = _maxyear )

¿How I can make the last measure to sum value from the first one in a row-wise style as a rolling sum function?

Re: Rolling Sum with SUMX not working as row-wise

Anonymous — Wed, 18 Oct 2023 19:55:31 GMT

Hii, once use the following DAX measure

RollingSum = VAR _fin=max(Calendario_forecast[Fecha])
VAR _days=max(Forecast_periodo[Forecast_periodo])
VAR _start=max(Calendario_forecast[Fecha])-_days
VAR _maxyear=max('Master Calendar'[Year])
VAR _laborables=
CALCULATE(sum('Master Calendar'[Working_day_flag]),
FILTER(all('Master Calendar'[Fecha]),'Master Calendar'[Fecha]>=_start&&'Master Calendar'[Fecha]<=_fin))
VAR _originalMeasure =
SUMX (
VALUES ( 'Master Calendar'[Year_month_num] ),
DIVIDE (
CALCULATE (
DIVIDE (
CALCULATE (
DIVIDE ( [Churned_contracts], [Active_Contracts] ),
FILTER (
ALL ( 'Master Calendar'[Date] ),
'Master Calendar'[Date] >= _start
&& 'Master Calendar'[Date] <= _fin
)
),
_working_days) * [Working_days_acumulative],
FILTER ( 'Master Calendar', 'Master Calendar'[Date] >= _fin )* ([Active_contracts]+[Future_contracts])
)
)

RETURN
SUMX (
ALL ( 'Master Calendar'[Date] ),
CALCULATE (
_originalMeasure,
FILTER (
ALL ( 'Master Calendar'[Date] ),
'Master Calendar'[Date] <= CURRENTROW()
)
)
)

Re: Rolling Sum with SUMX not working as row-wise

luisccmm — Thu, 19 Oct 2023 08:50:33 GMT

Hi Anonymous ,

Thanks for your answer but it is not working as expected, some reasons might be (not sure).
1) I See on your proposal a function that is not DAX language CURRENTROW()
2) On mi original formula the values are agregated in SUMX function by Yearmonth, whereas in your proposal you are using Date column

topic Re: Rolling Sum with SUMX not working as row-wise in DAX Commands and Tips

Rolling Sum with SUMX not working as row-wise

Re: Rolling Sum with SUMX not working as row-wise

Re: Rolling Sum with SUMX not working as row-wise