Finance/Accounting Data: Filling missing dates with adjusted Valuations

[KingTut]

Hi,

I have a data set with the relevant columns; Investment ID, Effective Date, Reported Valuation, Fundings, and Distributions.

• Investments can report valuations on a either a Quartelry, Semi Annual, or Annual Basis - typically on the last day of the respective period. Therefore, in a given year each investment can have a different number of valuation entries. You are always interested in having the latest valuation for any measured period or interval.
• During a given period, there can be any number of fundings (where you add cash to an investment) or Distributions (where you recieve cash from an investment). These can happen on any day during the year.

Investment ID	Effective Date	Reported Valuation	Funding	Distribution	Valuation Boolean
A	12/31/2020	$150	0	0	1
A	3/31/2021	$200	0	0	1
A	6/30/2021	$300	0	0	1
A	9/30/2021	$400	0	0	1
A	12/31/2021	$350	0	0	1
A	10/21/2021	0	$5	0	0
A	4/28/2021	0	$10	0	0
A	7/28/2021	0	0	$5	0
B	12/31/2020	$100	0	0	1
B	6/30/2021	$150	0	0	1
B	12/31/2021	$130	0	0	1
B	2/6/2021	0	$10	0	0
B	7/23/2021	0	$5	0	0
B	5/26/2021	0	0	$10	0
C	12/31/2020	$100	0	0	1
C	12/31/2021	$200	0	0	1
C	2/20/2021	0	$5	0	0
C	5/28/2021	0	$10	0	0
C	8/5/2021	0	0	$20	0

I would like this data to be able filter into a matrix that can drill into Monthly, Quartlerly, and Yearly levels. The issue is that when I pull the most recent valuation for a given period using the below function:

Most Recent End Period Rep Valuation = 
CALCULATE (
    SUM ( 'TH'[ReportedValuation] ),
    LASTDATE ( 'TH'[Effective Date] ),
    'TH'[Valuation Boolean] = 1
)

There are missing valuations for each respective investment and date (which occurs more frequently as you drill down the date hiearchy). When you reach the month level, all investments have missing valuations in atleast the first and second months of the quarter since none report that frequently. See Below:

Matrix with All Investments	Matrix with only Investment A
Matrix with only Investment B	Matrix with only Investment C

For the sake of tracking the valuation at any given time in the year, we use an Adjusted Value. An Adjusted Value is a way to estimate the financial value of the asset between its reporting dates. Its measured by taking:

(The last reported valuation)

+

(Fundings that have occured between the date of the last reported valuation and the current point of measurement)

-

(Distributions that have occured in the same period as the Fundings)

Using this calculation I can measure the intermediate valuation between periods to estimate my value up to the next repored valuation. To Illustrate:

• In the case of Investment C, it would be adding the running total of the fundings and distributions between 12/31/2020 and 12/31/21. that adjustment would have to happen for each investment for all the intervals its missing a valuation for. So for Investment A, it would be measuring the adjusted valuation for the second and third month. For Investment B, it would be measuring for the next five months.

So I need to find a way to use or generate an adjusted value for the blanks in the matrix. I've considered the following solutions:

• Use a measurement so that I can SWITCH() to use the Most Recent Reported Value() function OR use a new measurement that would calculate the correct adjusted value for all levels of the Date Hierarchy.
  
  • The issue I've had with this is solving for each group by Investment ID before aggregating through the Most recent function.
• Creating a new Table that will have adjusted values calculated through a power query

My PowerBI skills are very limited so would love help on solving this! mostly experienced in Excel and R.

PS: This a  very simplified version of the data set I'm using. My other data set has more than 500 investments with 20 years of data (all active for different periods with no 'end date' value). So a measurement solution would be preffered so my data aggregates dynamic.

[v-binbinyu-msft]

Hi @KingTut ,

I don't fully understand your demands, Could you please provide further explanation? And could you please give some concrete examples based on your calculation logic? a screenshot of your desired results is better.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu