Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys-
I have data that looks like this
In the XNPV Column, I am using this formula with 10% (.1) as the discount rate
Column = xnpv('Table 1', 'Table 1'[Profit], 'Table 1'[Dates], .1)
My problem: The XNPV is shown as a sum of the XNPVs of all the projects. I want it to be for the individual project, so specific to that individual row.
Any suggestions?
Thanks!
Solved! Go to Solution.
Alright guys- figured out a way that worked for me. Had to split numerator and denominator to make it work.
Made two columns one like this
XNPV DENOMINATOR = power(1.1,('Table'[Dates]-TODAY())/365)
= (1+i)^((Future Date-today)/365)
XNPV = CALCULATE(sum('Table'[Margin]))/'Table'[XNPV Denominator]
Not the fastest work around but works and got me what I needed.
Hope somebody else comes across this while browsing Google or something. This is pretty straightforward and considering we are discussing BI software, surprised that this does not have more optionality.
Hi @charles_o,
Try to createthe following measure:
XNPV = CALCULATE( SUM(Table[Profit]) * (0,1))
Since this is a measure it's based on context so on the project level it will give you the the discount value.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe discounted valued needs to take into account the date- else I would just multiply Profit*(1-.1)
The npv formula as written out on paper for example would be like this:
Profit/((1+.1)^((Date- todays_date)/365))
so for example if I plan to make $100 a year from today:
$100/((1+.1)^((2/13/2019-2/13/2018)/365)
$100/((1+.1)^(365/365)
$100/(1.1) = $90
But if it is half a year from today it would be this
$100/(1.1)^(182.5/365)
$100/(1.0488)= $95.346
Alright guys- figured out a way that worked for me. Had to split numerator and denominator to make it work.
Made two columns one like this
XNPV DENOMINATOR = power(1.1,('Table'[Dates]-TODAY())/365)
= (1+i)^((Future Date-today)/365)
XNPV = CALCULATE(sum('Table'[Margin]))/'Table'[XNPV Denominator]
Not the fastest work around but works and got me what I needed.
Hope somebody else comes across this while browsing Google or something. This is pretty straightforward and considering we are discussing BI software, surprised that this does not have more optionality.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |