Calculation with non-visulated data

[Anonymous]

Hello together

Here is my initial situation:

Value Date	Payment PC	IRR Purchased	Effective Duration
24-Okt-2022	-7'457'843.06	2.019226	6.97
25-Okt-2022	-14'827'458.33	1.367932	13.28
25-Okt-2022	-7'237'375.00	2.027469	8.52
25-Okt-2022	-17'468'035.65	1.358039	12.31
26-Okt-2022	-100'500'000.00	1.464718	14.21
27-Okt-2022	-6'982'216.67	1.288672	11.41
23-Nov-2022	-15'121'041.67	1.227285
23-Nov-2022	-161'291'111.11	1.227285
06-Dez-2022	-25'269'000.00	1.037119	16.47
13-Dez-2022	-2'454'739.68	1.361915	8.56
13-Dez-2022	-35'924'916.66	1.049181	11.27
13-Dez-2022	-2'484'160.00	1.04288	11.41
15-Dez-2022	-35'094'884.77	1.12798	12.18
15-Dez-2022	-30'135'718.18	1.118771	8.72
19-Dez-2022	-30'141'666.67	1.109334	8.70
19-Dez-2022	-35'303'666.66	1.074108	12.16
28-Dez-2022	-49'350'000.00	1.186102	16.32

I have daily data here, sometimes 2 entries per day (cf. month of November).

in the end, i want to have this format:

	MV	Yield	Duration
DEZ	-246'158'752.62	1.11	12.42
NOV	-176'412'152.78	1.23	0
OKT	-154'472'928.71	1.49	13.16

this is how the calculation works:

Value Date	Payment PC	Payment per month	Weighted Payment	IRR Purchased	Weighted Yield	Value Date	Effective Duration	Weighted Duration
23-Nov-2022	-15'121'041.67	-176'412'152.78	0.09	1.227285	0.11	23-Nov-2022		0
23-Nov-2022	-161'291'111.11	-176'412'152.78	0.91	1.227285	1.12	23-Nov-2022		0

Payment per month = SUM(Payment PC)         | from a single month

Weighted Payment =divide(table[Payment PC] , table[Weighted Payment])

Weighted Yield = Table[Weighted Payment] * Table[IRR Purchased]

Weighted Duration = Table[Weighted Payment] * Table[Effective Duration]

for all calculations the daily values have to be used, but i only want to visualise the monthly results. the monthly yield is = sum(Weighted Yield). does anyone know if this is possible? Preferably with measures? As it should be dynamic when i apply e.g. filters and not all data is needed.

Thanks a lot!

[johnt75]

The general mechanism is to create a summary table in a variable, which has the results of the measure at the day level, and then run an aggregation like SUMX or AVERAGEX over summary table. As an example, your weighted yield could be

Weighted Yield Monthly =
VAR SummaryTable =
    ADDCOLUMNS ( VALUES ( 'Date'[Date] ), "@value", [Weighted Yield] )
VAR Result =
    SUMX ( SummaryTable, [@value] )
RETURN
    Result

Although I put monthly in the name of the measure, it would actually work at any granularity of date.

[Anonymous]

Sorry, but I have not been working with Power BI for long and I think the formulas I have written are wrong.

I have already spent several hours with it and even with your approach I can't find a solution.