Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
25 | |
19 | |
15 | |
8 |
User | Count |
---|---|
58 | |
50 | |
44 | |
21 | |
19 |