Forum Discussion
Accumulated Measure using EARLIER and Inactive Relationship
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.
Thank you very much for the help,
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])
5 Replies
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])Hello Greg_Deckler ,
Thank you very much for the proposed solution. Unfortunately it returned the same results as my current formula.
Best regards,
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])
