DAX Union with SelectColumns

[AK_BA]

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.

Background

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:

1. Cashflow data:  XIRR dataset needs all cashflows that fall between dates from my date slicer (custom auto DateDim)
2. Valuation data:  If there is a beginning date that matches the min date on my slider, I need to get the beginning value.  Ending value is the exact opposite

Where I'm At

• I have all my datasets set up with proper relationships (they filter based on fund and my time slider just fine).
• I have a custom table started to UNION these datasets together
• I have a custom table TimeDim = CALENDARAUTO(), with MaxDate and MinDate measures

My Issue

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!

[Ashish_Mathur]

Hi,

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"?

[AK_BA]

Thank you Ashish - The valuation and cashflow data can be found here and here.

Net capital in this case is Ending NAV.

[Ashish_Mathur]

Hi,

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.

[AK_BA]

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.

[Ashish_Mathur]

Hi,

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.

[AK_BA]

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.

Thanks again

~AK_BA

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.