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 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.

 

 

 

 

 

 

 

 

 

 

 

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])
     )
     )

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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'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])
         )
         )