Lifetime Cumulative Total decreasing

[GarryFarrell]

Hi all,

I have a table (Table 2) that holds static values which will be the starting point for my decreasing cumulative total.

Then I have Table 1 which has the data. I made the change measure negative. The tables are joined by an ID.

I need help with a DAX formula that uses the staic start point and then uses then cumulative total to decrease the total as shown in the chart below. Thanks in advance everyone.

 

[Phil_Seamark]

Hi @GarryFarrell,

So long as you have a relationship betwen the two columns, please add this calculated column to your [Table1] and let me know how you get on

Cumulative Column = 
var StartingPoint = RELATED('Table2'[Starting Point])
var IDColumn = 'Table1'[ID]
var DateColumn = 'Table1'[Date]
var Result = StartingPoint + CALCULATE(
                                    SUM('Table1'[Change]),
                                    FILTER(
                                        ALL(Table1),
                                        'Table1'[ID] = IDColumn 
                                        && 'Table1'[Date] <= DateColumn
                                        )
                                        )

return Result

[GarryFarrell]

Hi Phil,

Thanks for the solution. It works. However when I use the full data set my PC runs out of memory. Do you think that a measure formula using the same theory would work any differently? I have removed unwanted columns from the queries to try to limit the amount of memory required. I'm running Power BI desktop 64-bit.

Regards,

Garry

[Phil_Seamark · Accepted solution]

Hi @GarryFarrell,

Here is the same idea written as a Measure.  Please let me know how that goes.

First bring the Starting Point value into Table1 using this calulcated Column

Starting Point = RELATED(Table2[Starting Point])

Then you can create the following measure:

Cumulative Measure = 
 MAX('Table1'[Starting Point])- 
 + 
 CALCULATE(
 SUM('Table1'[Change]),
 FILTER(
 ALL(Table1[Date]),
 'Table1'[Date]<=MAX('Table1'[Date])
 )
 )

[GarryFarrell]

Hi,

The measure doesn't calculate until it is used. So when designing it is much quicker. However they both cause me to run out of memory with the volume of data that I have and the available RAM.

Regards,

Garry