Calculate Investors Payout over time

[Anonymous]

hello everyone, i have a problem, first this is my pbix report https://1drv.ms/u/s!Ag9tIyk2ofNRjkB2irn8KlxTt-bj?e=x8mNvf
i made a table that shows my p/L report and i want next to it a table that shows the payout of investors. the pbix has everything you need but i will proviude with pictures to show you my desired result,



as you can see in example A, i chose the first week on my date tablw which is in 1/8/2022 till the following end of week.
then i made the calculations to how i want it,

after that in example B, you can see I chose the following week so the calculated payout must become last weeks payout added to the new week one so last weeks payout becomes the new initial value if i make sense,

final example is example C, which i jumped 2 weeks ahead so i want it to calculate the previous week which is week 3 to become the initial value and not week 2.

oh and i want to have on the investors table the last weeks payout too so the table visual on dax must look like

investors       initial amount      percentage      last week payout    this week payout

hope you can help me out

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

Total Payout = 
VAR Totalrevenue = Calculate(SUM(Revenue[Amount]),FILTER(ALL(DateTable),[Date]<Min('DateTable'[Date])))
Var Totalexpense = Calculate(SUM(Expenses[Amount]),FILTER(ALL(DateTable),[Date]<Min('DateTable'[Date])))
Var Grossprofit = (Totalrevenue - Totalexpense)
VAR Taxes = (Totalrevenue * 0.15)
VAR Netprofit = Grossprofit - Taxes
return MAX('Investors'[Initial Value])+Netprofit*MAX('Investors'[Percentage])

New Total Payout = 
VAR Totalrevenue = Calculate(SUM(Revenue[Amount]),FILTER(ALL(DateTable),[Date]<=MAX('DateTable'[Date])))
Var Totalexpense = Calculate(SUM(Expenses[Amount]),FILTER(ALL(DateTable),[Date]<=MAX('DateTable'[Date])))
Var Grossprofit = (Totalrevenue - Totalexpense)
VAR Taxes = (Totalrevenue * 0.15)
VAR Netprofit = Grossprofit - Taxes
return MAX('Investors'[Initial Value])+Netprofit*MAX('Investors'[Percentage])

Final output:

PS: I do not have access to your new sample, so I added a new table in your previous sample. If the result is not what you want, please provide with me more details or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

For further clarification the initial value miat only be used once for refer3nce to the new calculations so tjats why i only have it for specific date so august first inital is 10,000 for investor A and in the next day if i have positive net profit of 10,000 so x 10% is 1000, the new payout(initial value) should be 9000 from now on. I know its quite difficult to explain but i need your help guys