Using Date Slicer/Filter To Query Two Types of Dates Across Two Tables

[fordmichael20]

Long Question, appreciate any feedback

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.

All feedback would be greatly appreciated

[Eric_Zhang]

---

@fordmichael20 wrote:

Long Question, appreciate any feedback

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.

All feedback would be greatly appreciated

---

@fordmichael20

What is your "as at" table like? If in below case, you can unpivot those two columns to one.

If this is not your case, please be more specific by posting some sample data of your tables.

[fordmichael20]

@Eric_Zhang

Thanks for the input, this is not quite what I need. My table has one market value for each day, defining the beginning and ending is done with the filters. My as at table looks like this, for each portfolio, there will be a market value everyday (using sample excel files that are exactly the CSVs in PowerBI).

My transaction table will be linked to these portfolios so I want the filter I use on transactions, eg if I did for May 15-31 I want:

May 15: 17,486,960
May 31: 15,704,335

Thanks, sorry for the delay in response.