Forum Discussion

GarryFarrell's avatar
GarryFarrell
Advocate III
9 years ago
Solved

Lifetime Cumulative Total decreasing

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 measur...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
GarryFarrell
Advocate III
9 years ago

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's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    GarryFarrell
    Advocate III
    9 years ago

    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