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

Joining Dates - Creating Column or Row

I have the following two tables

Fund

Fund IDTermination Date
A6/25/2019
B6/24/2019

 

FUND EVALUATION

FUND IDUnitsPriceEffective Date
A500106/21/2019
A10096/24/2019
A100076/25/2019
B60066/21/2019
B800026/24/2019

 

I need help creating a measure or column that will calculate the market value of a fund at termination date.  To do this I need to join the date in the Fund and Fund Evaluation table.  How would I go about calculating the following

 

Fund A:  1000*7 = 7000

Fund B:  8000*2 = 16000

1 REPLY 1
Highlighted
Anonymous
Not applicable

Re: Joining Dates - Creating Column or Row

 

 

First step is to merge the queries on the Fund ID column. Select merge queries while you are viewing the Fund Evaluation table in the query editor. Expand it out so you can view the termination date.

 

pbi merge.PNG

 

 

 

 

 

Next, add a new column and input the formula:

 

Column = if( 'Fund Evaluation'[Effective Date] = 'Fund Evaluation'[Fund.Termination Date], 'Fund Evaluation'[Units] * 'Fund Evaluation'[Price], 0)

 
pbi calc.PNG
 
This should provide what you are looking for. Let me know if this helps of not.

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors