Forum Discussion

snayff's avatar
snayff
Regular Visitor
3 years ago

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

AmountJournal TypeReporting Date
15744P02/02/2023
63999G15/11/2023
31519P11/09/2023
76449G06/01/2023
59445P16/11/2023
90889G05/10/2023
84154P01/01/2024
85489G23/02/2023
58959P30/10/2023
68379P06/06/2023
67401G06/02/2023
28907P27/01/2023
69108G18/09/2023
82725P25/11/2023
38389P10/04/2023
60398G

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

  • snayff's avatar
    snayff
    Regular Visitor

    Any chance someone has any thoughts on this? 🤞