Forum Discussion
Calculation with non-visulated data
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!
2 Replies
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 ResultAlthough I put monthly in the name of the measure, it would actually work at any granularity of date.
