Help with DAX measure for cumulative percentage calculation

[ramz45]

Hello, I have a table shown below.The positive and negative adjustment values are percentages - ex - 4% , 5% etc.

I am trying to write a measure that takes a number (output of another table) and calculate the amount it increase or decreases based on the percentages.

Exaxmple - Say the number I am feeding is 10. The output of year 1 is 10 + 10*4/100 = 10.4.

The output of year 2 is 10.4 +10.4*5/100. And so on.  

I am unable to create a calculated column as the input number is not fixed. Can someone kindly help with drafting a measure for this?

[austinsense]

Give This a Try ... What you need is the cumulative product of the "Positive Adjustment" column which requires a combination of PRODUCTX with a Dynamic Filter for getting the right dates.

NumberTimesDynamicCumulativePercent =

// Figure out the Maximum Year dynamically, this is our anchor for running a cumulative percent.

VAR MaxYear = MAX(Table[Year])

// Find the relevant cumulative years based on the anchor. We get the relevant rows, add one to the %, multiply them together to get our cumulative %. The first argument to the FILTER function where I wrote table, may need to be ALL(Table). Dropping the variable in there may mean we can get away with just Table.

VAR CumPerc = PRODUCTX(
                           FILTER(Table, Year <= MaxYear),
                           1 + Table[Positive Adjustment]) 

// This is the measure from your other table
VAR Number = SUM(OtherTable[Number])

RETURN

// now we're just multiplying our two variables together
Number * CumPerc

[ramz45]

Thank you sir. I will try it out.

[ramz45]

Thank you Sir. I will try this out now

[BILASolution · Accepted solution]

Hi @ramz45

Try this...

Feeding = 10

Acummulative Percentage = 

var actualyear = FIRSTNONBLANK(Adjustment[Year];1)

return

[Feeding]*
CALCULATE
(
	PRODUCTX
	(
		ADDCOLUMNS(Adjustment;"Adjustmentt";1 + DIVIDE(Adjustment[Positive Adjustment];100));
		[Adjustmentt]
	) ; ALL(Adjustment);Adjustment[Year] <= actualyear
)

And the final result is...

I hope this helps

Regards

BILASolution

[ramz45]

That seems to have done the trick. I am just running a couple more validations, but looks really promising. Thanks again.

[Ashish_Mathur]

Hi,

1. Share the data layout of the other table
2. For the first year, why are you only using the numbers from the positive adjustment column.  Why not from the negative adjustment column?
3. Please show your expected result.

[ramz45]

1. Hello, The data in the other table is about 10 million records so here is a sample. I have page filters that limit the data to 1 firstname, last name combo. I plan on using a SUMX measure in to calculate the total payment.

SUMX('CMS Data','CMS Data'[Medicare Payment]*'CMS Data'[Service Count])

I was planning on using this caluculation in the measure to calculate cumulative %.

2. I will be using the values in the negative column as well. That will be a separate measure. Thought i could just replace the parameters from the measure for the positive column.

3. Sample output screenshot above

Thanks so much.