cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AK_BA
Frequent Visitor

DAX Union with SelectColumns

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:

irrDataSets.png

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!

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User III
Super User III

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Net capital in this case is Ending NAV.

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

relationship.png

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.

 

combinedTableFilters.png

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

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors