Forum Discussion

dswinden's avatar
dswinden
Helper II
3 years ago

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...
Need Help
  • dswinden's avatar
    dswinden
    Helper II
    3 years ago

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

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

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

      • dswinden's avatar
        dswinden
        Helper II
        3 years ago

        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?