Forum Discussion

SBIM's avatar
SBIM
Icon for Helper I rankHelper I
3 years ago
Solved

Rolling 12 Month Sum Not Working

I am trying to convert an Excel formula to Power BI that does a rolling 12 month sum of column. Here is the Excel version and what the data looks like.

=IF(H2<>"",SUMIFS(H:H,B:B,B2,C:C,C2,D:D,D2,E:E,">="&EOMONTH(E2,-12)+1,E:E,"<="&E2),"")

 

In PBI I have tried a dozen or more methods including DATESBETWEEN, DATESINPERIOD, indexing the months, etc. But nothing works out and I am at a loss as to why.

 

This in theory is the exact equivalent of the Excel formula.

Last 12 months LT =
CALCULATE(
    SUM(RawData[Lost Time]),
    RawData[Location] = EARLIER(RawData[Location]),
    RawData[Facility] = EARLIER(RawData[Facility]),
    RawData[Brand] = EARLIER(RawData[Brand]),
    RawData[Date] >= EOMONTH(EARLIER(RawData[Date]),-12)+1,
    RawData[Date] <= EARLIER(RawData[Date])
)

But as you can see it does not work either and only gives me only the amount in the given month.

 

Need Help
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    SBIM Try this, PBIX is attached below signature.

    Cumulative LT Measure = 
    VAR __Date = MAX('Table1'[Date])
    VAR __Division = MAX('Table1'[Division])
    VAR __Location = MAX('Table1'[Location])
    VAR __Facility = MAX('Table1'[Facility])
    VAR __Brand = MAX('Table1'[Brand])
    VAR __Table = 
        FILTER(
            ALLSELECTED('Table1'), 
            [Date] >= EOMONTH(__Date,-12) && [Date] <= __Date && 
            [Division] = __Division && [Location] = __Location && [Facility] = __Facility && [Brand] = __Brand
        )
    VAR __Result = SUMX(__Table,[Lost Time])
RETURN
    __Result

7 Replies

    • SBIM's avatar
      SBIM
      Icon for Helper I rankHelper I
      3 years ago

      I would actually disagree, calculate makes it easier to understand and allows more filter options than a SUMX where you have to nest multiple and statements to achieve a similar result. 

      But sadly this does not work for me either. It does the same thing where it only shows the lost time for the given month.

      Lost Time rolling sum =
      IF(
          ISFILTERED('Date'[Date]),
          ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
          VAR __LAST_DATE = ENDOFMONTH('Date'[Date].[Date])
          VAR __DATE_PERIOD =
              DATESBETWEEN(
                  'Date'[Date].[Date],
                  STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
                  __LAST_DATE
              )
          RETURN
              SUMX(
                  CALCULATETABLE(
                      SUMMARIZE(
                          VALUES('Date'),
                          'Date'[Date].[Year],
                          'Date'[Date].[QuarterNo],
                          'Date'[Date].[Quarter],
                          'Date'[Date].[MonthNo],
                          'Date'[Date].[Month]
                      ),
                      __DATE_PERIOD
                  ),
                  CALCULATE(SUM('RawData'[Lost Time]), ALL('Date'[Date].[Day]))
              )
      )

       

      And given this is done as a column in Excel I would imagine it is not supposed to be a Measure and should be a Calculated Column.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        3 years ago

        SBIM You have a single table data model and as such CALCULATE is going to cause problems for you as demonstrated in this video which is extremely similar to what you are dealing with. If you post sample data, I can probably knock this out in a jiffy. You are trying to use CALCULATE and TI functions in a single table data model and that is not a recipe for success.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    A potential solution is to make a duplicate table in power query to your data table, filter it to only show the last 12 months worth of data then use the values from that table. 

    It may be a bit messy but will work.