Hi - I am new to Power BI (coming from years with MSSQL and SSRS) and have a question regarding utilizing a combination of Union, SelectColumns and First/LastNonBlank.
I have been tasked with developing a report that calculates financial factors as well as IRR (XIRR to be specific). XIRR requires a data set that combines two of my data sets:
Where I'm At
I'm having a hard time combining just the first and last records from the valuation table with my cashflow table. I've tried making measures to capture this data, I've tried Frist/LastNonBlank, Calculate, and LookupValues with varying results (none of which actually met my requirements).
My Custom Table code:
ReturnData = SELECTCOLUMNS(vwCashflows,"dt",vwCashflows[EffectiveDate],"amt",vwCashflows[CashflowAmount],"secId",vwCashflows[SecurityId])
I had a UNION prior to removing the other selectedcolumns because they weren't bringing back good results.
Here is an example of my filtered data:
The combined table should have a 595,139.50 entry for 1/1/2017 and a 260,552.97 entry for 12/31/2018. Any guidance you can give to get me on the right track would be appreciated. My guess is that this is something simple I just haven't learned or run across yet.
Thanks for any help you can provide!
Solved! Go to Solution.
I am not sure of how much i can help but i'd like to try. Share both tables in a format that can be pasted in Excel. Also, is "Net capital", the "Ending NAV"?
This is the result i have got but am not very confident. So please share data for 2 securities and let me know what slicers/filters do you want to apply. Please also clearly show the expected result.
Ashish - that looks perfect!
I've put together datasets and results for two other securities here.
Slicers/filters I have taken care of. There is a slicer for the date (custom date table) and my various datasets are joined by securityId and date.
This is the result that i get. It matches with yours. I further need to check it for whether it will work with filter/slicers or not. So share the filters/slicers you will be applying on this visual and your expected results with those filters/slicers.
That is great news. Thank you Ashish.
This is just a preliminary report, I haven't gotten very far in finalizing anything. The relationships between the tables are below:
ReturnData is my combined table, TimeDim is my custom date table. Valuations and Cashflows are structured similarily to the data I sent prior. The Securities table contains the security id and the name of the security (sorry that I can't provide actual information to you).
The filters on the combined ReturnData table are super simple. There is a report level filter that utilzes the TimeDim table's Date field.
Users are able to select a time period and one or multiple securities, if multiple securities are selected, the combined table should contain all cashflows for the securities, plus an aggregated beginning and ending nav. If you could post the custom table formula I could add it to my model and let you know how it performs with my data.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps