topic Re: Show expected turnover for recurring payments in DAX Commands and Tips

Show expected turnover for recurring payments

Silvermountain — Sun, 27 Oct 2024 13:40:11 GMT

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 🙂

Re: Show expected turnover for recurring payments

lbendlin — Sun, 27 Oct 2024 18:06:53 GMT

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-Forum/ba-p/963216

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/1447523

Re: Show expected turnover for recurring payments

Silvermountain — Wed, 30 Oct 2024 16:50:49 GMT

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

Re: Show expected turnover for recurring payments

lbendlin — Thu, 31 Oct 2024 11:30:25 GMT

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

Re: Show expected turnover for recurring payments

Silvermountain — Mon, 04 Nov 2024 08:20:31 GMT

That's perfect, thank you very much!