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 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.

SBIM's avatar
SBIM
Helper I
3 years ago

Here is a link to a sample file with the Excel formulas I want to recreate in DAX. And really if you can just show me how to do the Cumalative LT I should be able to apply that to the rest of the measures they want.

 

Sample Data 

 

And I was wrong I do need them to be measures as the business needs to be able to select multiple brands, facilities, and locations and have this calculate it as a whole. Right now with the values in Excel it sums the values for each selection rather than calculates it as a single business unit.

  • 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.