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?
