Forum Discussion
Rolling Sum Not Working when Empty Data
Hello! I have used this formula in dozens of examples but for whatever reason it is not working for this example. I will try to explain this as best possible. I have a data source that I receiv...
dswinden Sure, just use SUMX instead of AVERAGEX, regardless the rolling part is the same.
Thanks for the help! I attempted this modifying for my fields (below) but do not get the result i need.
R2M Test =
VAR __EndDate = MAX('Distribution Listings'[Last Seen])
VAR __2MonthsAgo = EOMONTH(__EndDate, -2)
VAR __StartDate = DATE(YEAR(__2MonthsAgo), MONTH(__2MonthsAgo), 1)
VAR __Table =
SUMMARIZE(
FILTER(ALL('Date XREF'),'Date XREF'[Month Start]>=__StartDate && 'Date XREF'[Month Start]<=__EndDate),
'Date XREF'[Month Start],
"__Value",SUM('Distribution Listings'[Current stock])
)
RETURN
SUMX(__Table,[__Value])
the resulting data, is the same as using the formula i provided in my original post but will not show me the rolling inventory for future months. The outcome i need is that for January 2023, the Rolling 2 month inventory is January + December. Since there is 0 inventory in January and 9 in December, the result should be 9, but both the "DATESINPERIOD" approach above and your variable return the same result. Any ideas?