Power bi interest calculation every three months

[Sadiviedd]

2,25 value for 202208 is 20225 value of 100 * 9% / 4. the same for 202209 and 202210.
For 202211 we take value of 202208 month that is 102,25 (not 100, because interests were paid off) and that value 102,25 * 9% / 4 and we get 2,30. The same for 202212 and 202301.
For 202302 value we take 104,55 * 9% / 4 and as result get 2,35. Now 104,55 we get from 202211 and  202208 values, because 202211 value of 2,30 + 202208 value of 102,25 = 104,55.
How to do the same method in Power BI DAX when calendar is dynamic?

The point is that every 3 months from start date we get interests and for the first 3 months from start it is blank.

My tests of measures:

New monthly payment=

VAR minmonth_ = SELECTEDVALUE(Kalendorius[YearMonth])

VAR date_ = DATE(  LEFT(minmonth_, 4), RIGHT(minmonth_,2), 1)

VAR afterthreemonths = FORMAT( DATE( YEAR(EOMONTH( date_, 3)), MONTH(EOMONTH( date_, 3)), 1), "yyyymm")

VAR d_afterthreemonths = FORMAT( DATE( YEAR(EOMONTH(  MAX( Kalendorius2[Date] ), -3)), MONTH(EOMONTH(  MAX( Kalendorius2[Date] ), -3)), 1), "yyyymm")

VAR maxmonth = FORMAT( DATE( LEFT(minmonth_, 4) + [Period_in_years Value], RIGHT(minmonth_,2),1), "yyyymm")

VAR tbl =

    FILTER(

        ADDCOLUMNS(

                VALUES(  Kalendorius2[YearMonth] ),

        "Interests",

        IF( Kalendorius2[YearMonth] < afterthreemonths, [Additional payment],

            [Additional payment] + DIVIDE( (

                    CALCULATE( [Additional payment],

                Kalendorius2[YearMonth] = d_afterthreemonths) *

                [Interest_rate Value] ), 4, 0))),

    Kalendorius2[YearMonth] >= minmonth_ &&  Kalendorius2[YearMonth] <= maxmonth)

RETURN

SUMX( tbl, [Interests])

Kalendorius2 is Calendar

[daXtreme]

@Sadiviedd, a piece of advice.

From the code above I can see that the model is not adequate to the task at hand. In a good model (think: star schema with suitably created dimensions and fact tables), DAX is simple and fast. In a bad model the code is getting bigger and bigger, more and more cluttered and entities that should be held in tables are calculated on the fly. This is exactly what the code above is.

Please change the model if you want to have easy time maintaining this solution and even spotting errors.

Here's an article about what a good model is: Understand star schema and the importance for Power BI … (bing.com)

[Sadiviedd]

The point is that I want to make dynamic table. What I mean by that is I don't have fact tables from different sources, because everything is created based on filters selection. My goal is when customer choose start date for example: 202205 and investment period 1 year, I get dates list from 202205 to 202305. Also user have to choose what amount he wants to invest every month (so for example 100 eur) and yearly interest rate as %.

The rules are:

*when starting to invest the customer will get first interests after 3 months;

*for the FOURTH month he will reinvest not 100 eur, but 102,25 eur. (because he will get interest of 202205 month investment (100 eur). And so on, after three months he will get interest of 202208 investment (102,25) and reinvest not 102,25, but 104,55. (102,25 * 9% / 4 = 2,30 and 2,30 + 102,25 = 104,55)

I hope this makes more clear now 🙂