cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Opening balance as comparative issue

I had posted this moments ago but then in attempting to edit it appear to have deleted it!

 

I'm a long time lurker on the forums but have a questions I've not been able to find the answer for. I have a matrix table showing me investment positions by Manager>Security Type>Security. I have one column for current period end date (selected by Year>Quarter>Month slicers) and then another column showing a comparative (the comparative is selected by a slicer between prior month-end, quarter-end or year-end).

 

The issue I'm having is that while my subtotals for the comparative are correct, when I drill down to the security level my comparative column does not contain all securities. It is only showing those securities that are present at the current period end date (i.e. if a security has been sold it's value is included in the prior quarter security type total but the security does not show up in the list:

Capture.PNG

 

 

My current period and prior period measures are as follows (as I mentioned my comparative is selected from a switch slicer but the below measure is the prior quarter-end measure in the above example).

 

MV_Current Period End = CALCULATE(sum(FACT_FinancialData[BS.Base Market Value Clean]),LASTNONBLANK('Date Table'[Date],CALCULATE(SUM(FACT_FinancialData[BS.Base Market Value Clean]))))

MV_Prior Quarter End = OPENINGBALANCEQUARTER(sum(FACT_FinancialData[BS.Base Market Value Clean]),'Date Table'[Date])

The market value comes from a central fact table, the security description from a dimension table and the security type from a mapping table as follows:

Capture.PNG

 

 

Any help would be greatly appreciated!

7 REPLIES 7
Highlighted
Helper I
Helper I

Opening balance as comparative issue

Hi all,

 

Long time lurker, first time poster (mainly as I've always managed to find my answers in other posts). I'm having an issue with a matrix table comparing period ending balances from one month/quarter-end to another. I have security level information grouped by asset class.

 

One column for my current period balance (as selected by a Year - Quarter - Month slicer) and then another column for my comparative (I'm using a selection slicer and switch formula to change this comparative between the prior month-end, quarter-end or year-end).

 

The issue I'm having is that my sub-totals by asset class are correct, but when I drill down to the security level detail my prior period comparative does not contain all securities held at that prior period date. It is only showing rows for securities held at the current period date (i.e. where a security has been sold it does not show me a $0 current balance next to a >$0 balance for the prior period). Example below (I've blanked out names for confidentiality reasons).

Capture.PNG

Measures for my current and prior period market values are:

MV_Current Period End = CALCULATE(sum(FACT_FinancialData[BS.Base Market Value Clean]),LASTNONBLANK('Date Table'[Date],CALCULATE(SUM(FACT_FinancialData[BS.Base Market Value Clean]))))


UGL_Prior Quarter-End = OPENINGBALANCEQUARTER(sum(FACT_FinancialData[BS.Base Market UGL]),'Date Table'[Date])

 

My asset type is contained in a mapping table with the security name/description in a dimension table as follows:

Capture.PNG

 

Any help would be greatly appreciated!

 

 

 

Highlighted
Helper I
Helper I

Re: Opening balance as comparative issue

My assumption on this issue is because I am filtering by date and because the securities have been sold they are not present in the fact table for that particular date and as such are filtered out. I just can't for the life of me work out how to get my row level detail to show me those securities that were owned in the prior period.

Highlighted
Community Support
Community Support

Re: Opening balance as comparative issue

@JSummersgill,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Opening balance as comparative issue

Thank 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.

Highlighted
Helper I
Helper I

Re: Opening balance as comparative issue

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!

Highlighted
Microsoft
Microsoft

Re: Opening balance as comparative issue

Hi @JSummersgill,

Could you please share some sample file to have a test if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Opening balance as comparative issue

Hi @v-danhe-msft,

 

Thanks for the response. I think I accidently created this thread twice, but I've still yet to find a final solution (I did do a not very elegant work around for the time being). If I get chance I'll try and create a sanitised version of the file and attach it. Quite a lot of confidential information in the file currently.

 

Thanks

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors