Accumulated Measure using EARLIER and Inactive Relationship

[anmattos]

Dear colleagueas,

I'm trying to make this measure work for the past couple of days, but it seems over my head. I tried many things explianed elsewhere about accumulated measures, but it seems my case is somewhat special. I think I got pretty near the solution, but can't get it.

I need a MEASURE to calculate the accumulated total of another measure, as in the tabel below:

My current formula for "Total Accumulated" measure is:

=
CALCULATE (
    MAXX (
        fDCAs;
        COUNTROWS ( FILTER ( fDCAs; fDCAs[Rev] <= EARLIER ( fDCAs[Rev] ) ) )
    );
    USERELATIONSHIP ( dCalendar[Date]; fDCAs[Data Aprovação DCA] )
)

NOTE 1: I need a MEASURE, and not a CALCULATED COLUMN. This adds a bit of complexity when using EARLIER, since it needs a previous row context.

NOTE 2: The table has filters on the FILTER field and Slicers with DATES.
NOTE 3: For the TOTAL APPROVAL measure I have to use an inactive relationship between the CALENDAR table and the data table.

NOTE 4: I'm actually using PowerPivot.

The file XLSX file is available on the following link.

https://www.dropbox.com/scl/fi/tr2wuflc63jasjzw26kof/Accumulated-Calculation.xlsx?dl=0&rlkey=zj9g961...

Thank you very much for the help,

[Greg_Deckler]

@anmattos Try:

Total Approval =
  VAR __Rev = MAX('fDCA'[Rev])
  VAR __Table = 
    ADDCOLUMNS(
      DISTINCT('fDCA'[Rev]),
      "__TotalApprovals",[Total Approvals]
    )
RETURN
  SUMX(FILTER(__Table,[Rev]<=__Rev),[__TotalApprovals])

[anmattos]

Hello @Greg_Deckler ,

Thank you very much for the proposed solution. Unfortunately it returned the same results as my current formula.

Best regards,

[Greg_Deckler · Accepted solution]

@anmattos Forgot the ALL

Total Approval =
  VAR __Rev = MAX('fDCA'[Rev])
  VAR __Table = 
    ADDCOLUMNS(
      DISTINCT(ALL('fDCA'[Rev])),
      "__TotalApprovals",[Total Approvals]
    )
RETURN
  SUMX(FILTER(__Table,[Rev]<=__Rev),[__TotalApprovals])

[anmattos]

Wow!!!

Thank you @Greg_Deckler ! It worked! Now I´ll need more 3 days to understand it, but the problem is solved.

Thank you very much for your time!

[Greg_Deckler]

@anmattos Here's a walk-through:

Total Approval =
  VAR __Rev = MAX('fDCA'[Rev]) // Get the current value of Rev column in the current row
  VAR __Table = 
    ADDCOLUMNS(
      DISTINCT('fDCA'[Rev]), // Since we are in a visual where there is a single value of Rev in context, override this and get all the values of the distinct values of Rev in a single column table.
      "__TotalApprovals",[Total Approvals] // Add the measure to this single column table and the values returned are for the individual Rev values in the current virtual table row.
    )
RETURN
  SUMX(FILTER(__Table,[Rev]<=__Rev),[__TotalApprovals]) // SUM up the measure in our virtual table but filter the table such that only "previous" rows are summed.