Forum Discussion
Advocate IOpening balance as comparative issue
Community Support
You may take a look at this discussion.
http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373
Advocate IThank you for the reply v-chuncz-msft. I'll do some more detailed digging into this now but based on a quick read I'm not sure how I might adapt that issue into my current problem. My comparative dates are working fine at the aggregated level (i.e. it is showing me the correct current and prior period market values at the manager and security type sub-total level) but breaks down when I drill down to the detailed security level.
It feels to me that this might be quite a common thing that people want to see when comparing how portfolios/inventories change from one period to the next and underlying securities/products aren't constant.
I'm wondering if there's a way to essentially remove the row filter at the "Security Description" level which, in my original example, would probably show a lot of securities with $0 balances in both period, but then filter the visual to only show MV changes that are greater than or less than 0.
- JSummersgill
Another potential way to remedy the problem could be to create a blank record in my fact table for all the months following the month that a security is sold. That way when the matrix is filtered by date it will still return a record for those positions (with a $0 market value) and then the comparative number would be correct.
Not sure what the easiest way in the query editor would be to append blank records for each security for the months after it no longer appears in the source data (i.e. it has been sold).
Ripping my hair out!