Show expected turnover for recurring payments

[Silvermountain]

Hello fellow community,

We are a charity fund with income based on (amongst others) structural donations. Each month, quarter, half year or year someone will donate an amount to us. This is captured in a donations table, with the following columns:

- startdate

- enddate

- amount per donation

- frequency

If a donation will be done every quarter and starts in October, it will come back in January, April and July as well.

What I'd like to do is create a visual where I show the expected income per month. Donations start as of startdate and stop after reaching an enddate. I've tried to explain the same thing to copilot but without any luck. I hope someone in here knows how to pull this off 🙂

[Silvermountain]

Hi Ibendlin,

for example something like this: https://we.tl/t-s8MMWX5nZa 

In this case I expect:

- The first donation comes back every month

- The second every year, but only in March

- The 3rd every quarter (feb, may, aug and nov)

- The 4th every 6 months (jan and july)

Expected turnover in January would be €50, in februari €35 etc. But only if the enddate wasn't reached yet in that month+year

[lbendlin · Accepted solution]

Amt =
VAR a =
    SUMMARIZECOLUMNS (
        DateTable[Year],
        DateTable[MonthName],
        DateTable[Monthnumber],
        Donations[Donationnumber],
        Donations[Startdate],
        Donations[Enddate],
        Donations[Amount],
        Donations[Frequency],
        "md", MIN ( DateTable[Date] )
    )
VAR b =
    ADDCOLUMNS (
        a,
        "amt",
            IF (
                [md] IN CALENDAR ( [Startdate], COALESCE ( [Enddate], dt"2030-12-31" ) ),
                SWITCH (
                    [Frequency],
                    "Month", [Amount],
                    "Year", IF ( MOD ( [Monthnumber], 12 ) = MOD ( MONTH ( [Startdate] ), 12 ), [Amount] ),
                    "Quarter", IF ( MOD ( [Monthnumber], 3 ) = MOD ( MONTH ( [Startdate] ), 3 ), [Amount] ),
                    "Half year", IF ( MOD ( [Monthnumber], 6 ) = MOD ( MONTH ( [Startdate] ), 6 ), [Amount] ),
                    0
                )
            )
    )
RETURN
    SUMX ( b, [amt] )

see attached

[Silvermountain]

That's perfect, thank you very much!

[lbendlin]

That's a standard amortization pattern, and you do that in Power Query.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...