Rolling Sum Not Working when Empty Data

Question

Hello!&nbsp; I have used this formula in dozens of examples but for whatever reason it is not working for this example.&nbsp;I will try to explain this as best possible.&nbsp; I have a data source that I receive that has a date column called "Last Seen On".&nbsp; This is a count of inventory table, so if a product was last in stock on December 10th, thats the date that that will be in "Last Seen On".&nbsp;From there, I connected that date to a date table so that i can quickly filter by month, quarter, year, etc.So here is my dilemma.&nbsp; I want to measure the rolling 2 months inventory of a SKU.&nbsp; So using the above example, a product was last seen in inventory on December 10th.&nbsp; If i use January 2023 as my filter, then i should see the inventory of December 2022 and January 2023.&nbsp; Example below.&nbsp;The measure i am using (and have used dozens of time before) is ;&nbsp;CALCULATE(sum('Distribution Listings'[Current stock]),DATESINPERIOD('Date XREF'[Month Start],LASTDATE('Date XREF'[Month Start]),-2,MONTH))&nbsp;BUT the result i get when i use this measure is below.&nbsp; Because there is only two records for this item, and they occured in November and December, i only see the R2M results of Nov/Dec.&nbsp; I SHOULD see January 2023 showing 9 in current stock and then Feb 2023 showing 0 (as it does with the "Expected Result" column above&nbsp;&nbsp;&nbsp;

dswinden · Answer

Hi Pady, i show in my example above.&nbsp; I am trying to pull Jan 2023 rolling 2 months inventory.December 2022 had 9 unitsJanuary 2023 has 0 unitsR2M result should be 9.&nbsp; &nbsp; I have now solved my challenge, somehow my date table had duplicates so the relationship created was incorrectly created as Many to Many.&nbsp; Solved by removing duplicates and changing to Many to One

greg_deckler · Answer

dswinden&nbsp;Better Rolling Average - Microsoft Power BI Community

dswinden · Answer

Hi Greg_Deckler&nbsp;, thanks for sharing but i am looking for the total sales during that time, not just the average.&nbsp; Would your variable still work for that?

greg_deckler · Answer

dswinden&nbsp;Sure, just use SUMX instead of AVERAGEX, regardless the rolling part is the same.

dswinden · Answer

Thanks for the help!&nbsp; I attempted this modifying for my fields (below) but do not get the result i need.&nbsp;R2M Test = &nbsp; &nbsp; VAR __EndDate = MAX('Distribution Listings'[Last Seen])&nbsp; &nbsp; VAR __2MonthsAgo = EOMONTH(__EndDate, -2)&nbsp; &nbsp; VAR __StartDate = DATE(YEAR(__2MonthsAgo), MONTH(__2MonthsAgo), 1)&nbsp; &nbsp; VAR __Table = &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(ALL('Date XREF'),'Date XREF'[Month Start]&gt;=__StartDate &amp;&amp; 'Date XREF'[Month Start]&lt;=__EndDate),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date XREF'[Month Start],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "__Value",SUM('Distribution Listings'[Current stock])&nbsp; &nbsp; &nbsp; &nbsp; )RETURN&nbsp; &nbsp; SUMX(__Table,[__Value])&nbsp;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.&nbsp; The outcome i need is that for January 2023, the Rolling 2 month inventory is January + December.&nbsp; 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.&nbsp; Any ideas?&nbsp;&nbsp;&nbsp;

Forum Discussion

Rolling Sum Not Working when Empty Data

8 Replies

Related content

Rolling accumulation

Rolling accumulation

Rolling 3 months not working properly

Rolling Date Identifiers

Empty visual