I have a NPV calculation which I've done manually because the XNPV function doesn't seem to work.
The NPV measure I've created looks like this:
'Data'[Undiscounted Value] / (1+'Discount Rate'[Discount Rate Value])^([Component]/12)
I've discovered the error with my calculation lies in the [Component] portion of the measure. The [Component] calculation is as follows:
IF(TODAY() > LASTDATE('Data'[Date]), BLANK(),
DATEDIFF(TODAY(), LASTDATE('Data'[Date]), MONTH))
The [Component] calculation is intended to determine the number of months for the NPV calculation. I'm dividing by 12 because I have an annual discount rate, but my data is in months.
The above calculation works perfectly in a line/bar chart. I have spot tested the months to confirm that the values are correctly discounted.
The PROBLEM occurs when I attempt to AGGREGATE the data. In other words, it works fine in a line chart, but when I just put the calculation on a card visual it uses the LASTDATE for the component and performs the calculation using the sum of all undiscounted values. In other words, it does something like this:
SUM ('Data'[Undiscounted Value] ) / (1+'Discount Rate'[Discount Rate Value])^(MAX(Component) /12)
and then spits out a result which is incorrect.
What it is supposed to be doing is simply taking the sum of all the discounted values. So running the calculation month by month and then adding all the months, but instead it's adding the Undiscounted values and then discounting them over 36 months.
My QUESTion is: is there an alternative function that would tell PowerBI not to use the LASTDATE, but instead iterate over each month (row) and then SUM
