I have a data set that captures financial data for the last 6 financial year. For each produt I have one line per financial month end period, along with the balance sheet value, and revenue data. As an example the data looks something like this:
Product Financial Year Period Balance Sheet Revenue
Product 1 2012 Jan 2012 10 1
Product 1 2012 Feb 2012 11 3
Product 1 2013 March 2012 15 -2
And so on....
What I am trying to do within PowerBI is to create one page where the users can use filters to view the data by Financial year and month end date, however in a few different ways:
Essentially, I want filters for them to choose the year and month end they are interested in, and then choose how they want to see the information.
Where I am tripping up is that for Balance Sheet data you only ever want to show the value as at the specific month chosen, however the revenue will be cummulative YTD / QTD / Monthly up until the month selected within the particular financial year.
In the exmaple, if the Choose Feb 2012, Balance sheet value should show 11, but YTD revenue should show 4. (1 + 3) Similarly for March 2012 Balance sheet should show 15 and YTD revenue should show 2 (1+3 - 2) or monthly revenue should show -2.
Does anybody have any advice on how to achieve this?