The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello guys,
I need to Create a Measure (VLR TOTAL) according to the image below:
Solved! Go to Solution.
Hey,
some things moved faster as assumed, and here you will find my solution that now considers also the portfolio if filtered. If the portfolio is not filtered, the calculation is also executed on the level of detail, this is to avoid using wrong interest rates and investments.
Here is an Excel file that helped me to cross check the measure, set the cell c2 to FALSE if the interest rate should not be applied in the first period of the investment.
What happens within in the measure CI is basically this
Create a table that is used in SUMX
The check I mentioned above looks like this
IF ( DATEDIFF ( DATE ( YEAR ( vInvDate ), MONTH ( vInvDate ), 1 ), 'Interest'[DateValue], MONTH ) = 0, // use just 1 if no interest rate should be applied in the period of the investment 1 + 'Interest'[InterestRate], 1 + 'Interest'[InterestRate] )
Finally use SUMX(the table described above, Inv_Compound)
Hope this is what you were looking for
Hey,
some things moved faster as assumed, and here you will find my solution that now considers also the portfolio if filtered. If the portfolio is not filtered, the calculation is also executed on the level of detail, this is to avoid using wrong interest rates and investments.
Here is an Excel file that helped me to cross check the measure, set the cell c2 to FALSE if the interest rate should not be applied in the first period of the investment.
What happens within in the measure CI is basically this
Create a table that is used in SUMX
The check I mentioned above looks like this
IF ( DATEDIFF ( DATE ( YEAR ( vInvDate ), MONTH ( vInvDate ), 1 ), 'Interest'[DateValue], MONTH ) = 0, // use just 1 if no interest rate should be applied in the period of the investment 1 + 'Interest'[InterestRate], 1 + 'Interest'[InterestRate] )
Finally use SUMX(the table described above, Inv_Compound)
Hope this is what you were looking for
Thank you very much!
Worked perfectly.
I will study deeply the logic that compose this measure.
Thank you so much!
Hi @fjjohann,
Following from other replies, you basically need to calculate the cumulative product of your 'growth factors'.
Gerhard Brueckl's blog (link here) had a method using summing logarithms, then mentioned that you can now use PRODUCTX.
According to your description above, you may need to do a Recursive Calculations in Power BI using DAX. Here is a similar thread, could you go to check if it helps in your scenario?
Regards
Thank you very much for your reply @v-ljerr-msft.
I analyzed the link and a following formula served me in parts:
Sales ForeCast PRODUCTX: = IF (ISBLANK ([Sales]), CALCULATE (
PRODUCTX (
VALUES ('Date'),
[MultiplyBy]),
DATESBETWEEN ('Date' [DateValue], BLANK (), MAX ('Data' [DateValue]))
),
[Sales]
)
I need to consider a monthly deposit amount to be aggregated into the calculation.
I could not find a way to do that.
You would know?
Hey,
maybe this can be of some helps, I "translate" your monthly deposit into my "investments" that I described some days ago:
https://docs.com/minceddata/3687/dax-using-table-iterators-to-calculate-a-future?c=B13yYP
Hope this is what you are looking for
Thank you very much @TomMartens
Your measurements work very well.
However, I am not able to take into account the following:
I have a column named Portfolio in both the InterestRates Table and the Investment Table.
I can not put Porfolio as a panel filter. The CI measure calculates all table values independent of the filter.
Hey,
can you please prepare an EXCEL, that contain a minimum timeseries to show your requirement. And share the link to the Excel file, preferrable on OneDrive or Dropbox, because I've encountered problems with other fileshares.
Cheers
Hey,
thanks for sharing some data, I will have a closer look in the evening today, but I'm quite busy the next days.
So it can take until the weekend
Can you please add the expected result to your excel file.
And one question: I assume that the interestrate is a monthly one?
Cheers
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
137 | |
110 | |
106 | |
75 | |
62 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |