Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
wjkolesar
Advocate II
Advocate II

NPV Not Aggregating / Summing from Month to Year Correctly

Hi - 

I am trying to calculate NPV allowing end users to select their Discount Rate and Date Range.

 

I can get the values correct at a monthly level when spanning multiple years (IE 2022 & 2023), but when trying to aggregate up to the year level, the monthly values for 2023 are not summing correctly.

 

The XNPV function is also not working correctly at the monthly row level, but the total is correct, so I needed to do a SUMX function to get the calculation correct at the row and total levels.  Example table is below, with measure calculation statements following it.  The measure that is accurate is the Billings - Discount Rate - FINAL (I have a 6% discount rate currently applied).

wjkolesar_0-1668709332775.png

 

Billings - No Discount Rate = SUM ( Billings[Billings] )


Billings - Discount Rate = 
DIVIDE ( 
    Billings[Billings - No Discount Rate],
    POWER ( 
        SELECTEDVALUE ( 'Rates Table'[1 + Discount Rate] ),
        [# of Days Divided by 365]
    )
)


Billings - Discount Rate - XNPV = 
XNPV ( Billings, [Billings - No Discount Rate], Billings[Date], SELECTEDVALUE ( 'Rates Table'[Value] ) )


Billings - Discount Rate - FINAL = 
VAR _Table =
    SUMMARIZE (
        'Billings',
        'Billings'[Date],
        "Billings - Discount Rate Value", [Billings - Discount Rate]
    )
RETURN
    IF (
        HASONEVALUE ( 'Billings'[Date] ),
       [Billings - Discount Rate],
        SUMX ( _Table, [Billings - Discount Rate Value] )
    )

 

 

When aggregating this up to the year level, I would assume that the total for 2023 would be $727,405 (just summing up all 2023 rows from the above screenshot).  However, this is my result:

wjkolesar_3-1668709624387.png

 

Any ideas / suggestions on how to fix this?

 

Thanks in advance!

1 ACCEPTED SOLUTION

thanks.

 

so i think the problem is 2 fold.

 

Firstly I do believe the number to be 734639.0506839028 is correct, not $727,405.  The issue with your measures (all the discount rate ones), all of them are set to currency with no decimal places.  Because you are using the power of the 1+ Discount rate which is a decimal you are using a lot of the accuracy because you are rounding.  So i set the discount rate to decimal places of 4, and when did that the sum of that table was correct in excel.   However your totals are stilll not working properly.

 

vanessafvg_0-1668809466095.png

 

second issue is that in order to get the correct total that is reflected when you sum it in excel, you need to iterate through each line to get your calculation  ie

 

so I changed this calculation to this

 

Billings - Discount Rate =
SUMX (
VALUES ( Billings[Date] ),
DIVIDE (
Billings[Billings - No Discount Rate],
POWER (
SELECTEDVALUE ( 'Rates Table'[1 + Discount Rate] ),
[# of Days Divided by 365]
)
)
)

 

not quite sure what you were trying to achieve with the discount rate final but please check what i have done in discount rate.

 

please see attached.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
vanessafvg
Super User
Super User

can you share some data in text format?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg - sure, here is the sample data I am using, thanks!

 

DateBillings
11/30/2022 $               50,000.00
12/31/2022 $               75,000.00
1/31/2023 $               60,000.00
2/28/2023 $               50,000.00
3/31/2023 $               50,000.00
4/30/2023 $               75,000.00
5/31/2023 $               60,000.00
6/30/2023 $               65,000.00
7/31/2023 $               50,000.00
8/31/2023 $               75,000.00
9/30/2023 $               60,000.00
10/31/2023 $             100,000.00
11/30/2023 $               50,000.00
12/31/2023 $               60,000.00

I will also need this

 

 SELECTEDVALUE ( 'Rates Table'[Value] ) )

this rates table; 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg - sorry about that, this is just a table I created in PBI Desktop with the GenerateSeries function: 

 

Rates Table = GENERATESERIES(0.0,.2025,0.0025)
 
And then I added new column: 
1 + Discount Rate = 1 + 'Rates Table'[Value]

apologies but im actually struggling to recreate your scenario

 

i am missing how you get 

 

no of days

no of days divided by 365 

etc, in order to replicate the issue i need your whole file, can you provide it?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




thanks.

 

so i think the problem is 2 fold.

 

Firstly I do believe the number to be 734639.0506839028 is correct, not $727,405.  The issue with your measures (all the discount rate ones), all of them are set to currency with no decimal places.  Because you are using the power of the 1+ Discount rate which is a decimal you are using a lot of the accuracy because you are rounding.  So i set the discount rate to decimal places of 4, and when did that the sum of that table was correct in excel.   However your totals are stilll not working properly.

 

vanessafvg_0-1668809466095.png

 

second issue is that in order to get the correct total that is reflected when you sum it in excel, you need to iterate through each line to get your calculation  ie

 

so I changed this calculation to this

 

Billings - Discount Rate =
SUMX (
VALUES ( Billings[Date] ),
DIVIDE (
Billings[Billings - No Discount Rate],
POWER (
SELECTEDVALUE ( 'Rates Table'[1 + Discount Rate] ),
[# of Days Divided by 365]
)
)
)

 

not quite sure what you were trying to achieve with the discount rate final but please check what i have done in discount rate.

 

please see attached.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thank you!  The Billings - Discount Rate - FINAL was doing pretty much what your new Billings - Discount Rate calculation is doing.  It was iterating over the date with SUMX so that the monthly row values were accurate, along with the total.  So afterall, there is no issue here - just with all the rounding occuing, it threw off what I was expecting.  Thanks again.  

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.