Forum Discussion
Show expected turnover for recurring payments
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 🙂
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
4 Replies
- lbendlin
Super User
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 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
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
That's perfect, thank you very much!
