Forum Discussion
Cumulative Sum Stops Working with Other Filter Condition
Objective
I am trying to calculate the cumulative value of a number, using dates as a measure of progress, n = day1, n1 = day1+day2 etc., and apply a secondary filter.
Problem
When adding in another filter, beyond the date, the cumulative sum stops working, returning only the value for the earliest date - or not at all.
Actions so Far
I have tried using variations of `KEEPFILTER` and `ALLSELECTED` to see if I could get the secondary filter to apply, but these didnt work.
Details
The cumulative count of Actuals works just fine on its own:
But as soon as I add the Cost Type column `ActualsCum` only returns a value for the first date for 2 of the 3 Cost Types (Internal has no values).
`Actuals` still works, which is what leaves me most confused as `ActualsCum` is using `Actuals`.
Here are the measures in question:
Actuals =
// get the actuals from Transactions table for all providers
VAR internal =
CALCULATE(
SUM(factTransactions[Amount]),
USERELATIONSHIP(factTransactions[Reporting Date], dimCalendar[DateID]),
factTransactions[Journal Type] = "G"
)
VAR external =
CALCULATE(
SUM(factTransactions[Amount]),
USERELATIONSHIP(factTransactions[Reporting Date], dimCalendar[DateID]),
factTransactions[Journal Type] = "P"
)
RETURN (internal + external) * -1 // finance use negative numbers, so flip to positive
ActualsCum =
// cumulative Actuals
CALCULATE(
[Actuals],
USERELATIONSHIP(factTransactions[Reporting Date], dimCalendar[DateID]),
FILTER(
ALL(dimCalendar),
dimCalendar[DateID] <= MAX(dimCalendar[DateID])
)
)
Sample data
factTransactions
| Amount | Journal Type | Reporting Date |
| 15744 | P | 02/02/2023 |
| 63999 | G | 15/11/2023 |
| 31519 | P | 11/09/2023 |
| 76449 | G | 06/01/2023 |
| 59445 | P | 16/11/2023 |
| 90889 | G | 05/10/2023 |
| 84154 | P | 01/01/2024 |
| 85489 | G | 23/02/2023 |
| 58959 | P | 30/10/2023 |
| 68379 | P | 06/06/2023 |
| 67401 | G | 06/02/2023 |
| 28907 | P | 27/01/2023 |
| 69108 | G | 18/09/2023 |
| 82725 | P | 25/11/2023 |
| 38389 | P | 10/04/2023 |
| 60398 | G | 26/05/2023 |
dimCostType
| CostType |
| Internal |
| External |
The `dimCalendar` table is a long series of dates, 1 date per day.
Any help with this would be greatly appreciated.
1 Reply
- snayffRegular Visitor
Any chance someone has any thoughts on this? 🤞