Cumulative Total Not Working

[pjmorris]

Hi, this should be very simple, but I can't work it out. I'm trying to create a cumulative balance from a simple income and expenditure sheet filtered by date using a qryCalendar in PowerPivot.

I have the following measures:
1. [Credits]=sum(qryTransactions[Credit])
2. [Debits]=sum(qryTransactions[Debit])
3. [earliest]=min(qryCalendar[Date])
4. [latest]=max(qryCalendar[Date])
5. [balance]=[Credits]-[Debits]
and
6. [CumBal]=CALCULATE([Credits]-[Debits],
ALLSELECTED(qryCalendar),
FILTER(qryCalendar,qryCalendar[Date]<=[latest]))

The calendar is linked to the transaction table in PowerPivot.

If I delete the Filter clause from the [CumBal] measure it successfully calculates the cumulative balance across the entire date range. My logic is then that applying the Filter Clause should exclude all more recent transactions and therefore deliver a cumulative result. The first table below shows what happens without the FILTER clause '[CumBal]=CALCULATE([Credits]-[Debits], ALLSELECTED(qryCalendar))', (rows 7 & 14 are the problem measure):

1st Lifton Scouts Accounts - 2021 - New Version.xlsm
	A	B	C
1		Calendar
2	Row Labels	2016	2017
3	Beavers
4	Credits	£2,142.50	£2,754.50
5	Debits	£955.99	£1,396.85
6	Balance	£1,186.51	£1,357.65
7	CumBal	£2,544.16	£2,544.16
8	earliest	05 Apr 16	05 Apr 17
9	latest	04 Apr 17	04 Apr 18
10	Cubs
11	Credits	£2,820.26	£2,558.15
12	Debits	£1,275.44	£1,355.05
13	Balance	£1,544.82	£1,203.10
14	CumBal	£2,747.92	£2,747.92
15	earliest	05 Apr 16	05 Apr 17
16	latest	04 Apr 17	04 Apr 18
Sheet1

and the second is the result with the measure as shown at 6. What I should be getting in cells c7 and c14 is £2544.16 and £2747.92 respectively. It appears to be fully filtering by date, but I expected the ALLSELECTED(qryCalendar) to resolve that. I've even tried changing to FILTER(ALLSELECTED(qryCalendar),qryCalendar[Date]<=[latest]) but that then ignores the [latest] date. I've tried disconnecting the tables and that didn't work either.

1st Lifton Scouts Accounts - 2021 - New Version.xlsm
	A	B	C
1		Calendar
2	Row Labels	2016	2017
3	Beavers
4	Credits	£2,142.50	£2,754.50
5	Debits	£955.99	£1,396.85
6	Balance	£1,186.51	£1,357.65
7	CumBal	£1,186.51	£1,357.65
8	earliest	05 Apr 16	05 Apr 17
9	latest	04 Apr 17	04 Apr 18
10	Cubs
11	Credits	£2,820.26	£2,558.15
12	Debits	£1,275.44	£1,355.05
13	Balance	£1,544.82	£1,203.10
14	CumBal	£1,544.82	£1,203.10
15	earliest	05 Apr 16	05 Apr 17
16	latest	04 Apr 17	04 Apr 18
Sheet1

I simply can't see what is probably right before my eyes and any suggestions will be gratefully received.