Trouble with Running Total

[GiantRectangle]

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 Is shown calculated correctly. I can't seem to get PBI to do this:

[Date Week Ending]       [Code]  [ACT Productivity] [**bleep** Productivity]

1/24/2021 0:00	3.8602	1.309642857	1.309642857
2/14/2021 0:00	3.8602	10.88947368	6.099558269
1/9/2022 0:00	3.7112	2.525	2.525
1/16/2022 0:00	3.7112	3.833333333	3.179166667
2/27/2022 0:00	3.7122	9.814285714	9.814285714
3/13/2022 0:00	3.7452	4.0453125	4.0453125
3/20/2022 0:00	3.7452	4.633082707	4.339197604
4/17/2022 0:00	3.7112	2.192	2.850111111
4/17/2022 0:00	3.7122	11.03333333	10.42380952
4/17/2022 0:00	3.8112	2.459504132	2.459504132
4/24/2022 0:00	3.7112	1.563948498	2.528570458
4/24/2022 0:00	3.7122	11.35	10.73253968
5/1/2022 0:00	3.7112	2.818930041	2.586642374
5/1/2022 0:00	3.7122	3.04	8.809404761
5/1/2022 0:00	3.8112	1.369369369	1.914436751
5/8/2022 0:00	3.7112	1.905336427	2.473091383
5/8/2022 0:00	3.7122	3.536585366	7.754840882
5/8/2022 0:00	3.8112	1.275	1.701291167
5/8/2022 0:00	3.8122	2.2	2.2
5/15/2022 0:00	3.7112	3.393125	2.604524757
5/15/2022 0:00	3.7112	3.88125	2.764115412
5/15/2022 0:00	3.7122	9.416058394	8.031710467
5/15/2022 0:00	3.7142	8.360294118	8.360294118
5/15/2022 0:00	3.8112	2.516129032	1.905000633
5/15/2022 0:00	3.8112	2.541935484	2.032387603
5/15/2022 0:00	3.8122	4.6	3.4
5/22/2022 0:00	3.7112	2.75862069	2.763504888
5/22/2022 0:00	3.7122	10.17	8.337180401
5/22/2022 0:00	3.7142	8.888291747	8.624292933
5/22/2022 0:00	3.8112	4.490909091	2.442141185
5/22/2022 0:00	3.8122	4.675	3.825
5/22/2022 0:00	3.8142	8.620833333	8.620833333

Here's the measure I've got:

**bleep** Productivity =

VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date

VAR ThisCode = SELECTEDVALUE( data[Code] ) -- Saves the code for the line

RETURN

CALCULATE (AVERAGE(data[ACT Productivity]), -- Average productivity

'Date'[Date] <= MaxDate,        -- Where date is before the last visible date

data[Code] = ThisCode,           -- Where code is the same as this line

ALL ( Date )                              -- Removes any other filters from Date

)

And here's a snip of the result. As you can see, [**bleep** Productivity] is coming through exactly the same as [ACT Productivity]:

What am I doing wrong?

[GiantRectangle]

Thanks, tamerj1! That did it!

[tamerj1 · Accepted solution]

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
    )