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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fordmichael20
Frequent Visitor

General Table and Column Creation Using Different Dates

Hi, I've asked this before and I am unable to figure this out

 

I work with portfolio information. I'm bringing in two tables. One is transaction based, with each row having a date to reference a transaction. The other is an 'as at' date piece of information that has total market value of the portfolio. The goal I'm trying to achieve is the following:


- Allow the user to select a date range on the report/dashboard (for example let's say she chooses March 1, 2017 to March 31 2017)
- The table with transactions will be filtered between these dates; this I'm able to do in Power BI with no problem if I select transaction date as the date slicer/filter field
- The table with the 'as at' information will show twice, once in one column (beginning market value) as at March 1, 2017 and once again in another column as at March 31, 2017 (ending market value)


There are a few more nuances but as you may have guessed the goal is to take the beginning value, account for the transactions, and ensure the ending value makes sense.


I think I need to make a joined date table but I'm having trouble getting it to work.

I've attached three photos, the first (Trans list) shows the transactions by portfolios, the total market value (total MV), the third is a table I've created, a sum table where, by portfolio, then by transaction type, summing the cash from the trans list. This is where I think my problem is. I am trying to do the following:

1. With a date slicer, link this table such that when I change my dates, it recalculates the sum table for these dates based on the trans list dates within the range
2. With the same date slicer the following:
    a. the MV1 column will pull the value from the total MV table from the "from" (first) date selected from the slicer
    b. the MV2 column will pull the value from the total MV table from the "to" (last) date selected from the slicerSum Table.PNGtotal MV.PNGTrans list.PNG


All feedback would be greatly appreciated

3 REPLIES 3
CahabaData
Memorable Member
Memorable Member

I am going to ask my question not in terms of Power BI features but in regard to the data set that you are starting with - in that I've written database applications in this type finance in the past; 

 - I see the top table with 3 fields; date, NAV, multiple Portfolios

- I see the bottom table with 5 fields has a Date, Trans type, and a Cash Amount, single Fund. 

 

So my question is in terms of your dataset: are these two tables directly related by a common transaction ID (not just a generic transaction type ID)?  i.e. each transaction (lower table) results in a new NAV (upper table).   This would be a 1:1 relationship.

 

 

www.CahabaData.com

Hi,

 

@CahabaData

 

I've taken a new strategy for this, I'm using the dates from the transaction table as measures and then using them in my column. My measures look good in the visual view, I've called them RD1 and RD2. However I'm finding them inconsistent when used in my table. Here is my definition for them, the values get shown as I'd like in the visual view:

 

RD1.PNGRD2.PNG

 

However, in the column/table view these never change, they only reference the first and last dates in the table from which they're pulled (RD column). Why doesn't this flow back to the table view (note that I added a column just to show what the measures were as I worked through.

 

RD Table View.PNG

Hey @CahabaData

 

First, thanks for the response.

 

Secondly, no, there would be no linkage on transaction ID.

I would be basing this off of the Portfolio code. There is a table in between that has the client, one portfolio per row that I use. See attached.

Essentially I'm just having trouble applying the date filter I want to work on this created table. I've created a non-dynamic view where the date filter has no effect, I want to go to the next step but I'm having trouble.

Data Relations.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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