Forum Discussion

GiantRectangle's avatar
GiantRectangle
Regular Visitor
4 years ago
Solved

Trouble with Running Total

Hi all, What I'm trying to do is average [ACT Productivity] with matching [Code] for all dates less that or equal to the date for the selected item. Here's my data, where [**bleep** Productivity] is...
  • tamerj1's avatar
    tamerj1
    4 years ago

    hi GiantRectangle 

    please try

    **bleep** Productivity =
VAR MaxDate =
    MAX ( 'Date'[Date] ) -- Saves the last visible date
RETURN
    CALCULATE (
        AVERAGE ( data[ACT Productivity] ),
        -- Average productivity
        'Date'[Date] <= MaxDate,
        -- Where date is before the last visible date
        ALLEXCEPT ( data, data[Code] ),
        -- Where code is the same as this line
        ALL ( Date ) -- Removes any other filters from Date
    )
tamerj1's avatar
tamerj1
Community Champion
4 years ago

hi GiantRectangle 

please try

**bleep** Productivity =
VAR MaxDate =
    MAX ( 'Date'[Date] ) -- Saves the last visible date
RETURN
    CALCULATE (
        AVERAGE ( data[ACT Productivity] ),
        -- Average productivity
        'Date'[Date] <= MaxDate,
        -- Where date is before the last visible date
        ALLEXCEPT ( data, data[Code] ),
        -- Where code is the same as this line
        ALL ( Date ) -- Removes any other filters from Date
    )