Forum Discussion

SBIM's avatar
SBIM
Helper 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...
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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
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
SBIM's avatar
SBIM
Helper I
3 years ago

That worked, thank you for the assistance.