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):
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.
I simply can't see what is probably right before my eyes and any suggestions will be gratefully received.
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.
