Forum Discussion
Accumulate calculated value over time
- Anonymous4 years ago
i finally found a solution for me and i will guide you through the steps :
first i created a net profit measureNet Profit = VAR Totalrevenue = Calculate(SUM(Revenue[Amount])) VAR Payroll = CALCULATE(SUM(Expenses[Amount]),FILTER(Expenses,Expenses[Category] = "Payroll Expenses")) VAR Business = CALCULATE(SUM(Expenses[Amount]),FILTER(Expenses,Expenses[Category] = "Business Expenses")) VAR Financial = CALCULATE(SUM(Expenses[Amount]),FILTER(Expenses,Expenses[Category] = "Financial Fees")) Var Totalexpense = Calculate(SUM(Expenses[Amount])) Var Grossprofit = (Totalrevenue - Totalexpense) VAR Taxes = (Totalrevenue * 0.15) VAR Netprofit = Grossprofit - Taxes Return Netprofit
later i created a "before" measure that calculates all net profit from my inital date to selected date in the following measure :before = VAR seldate = MAX(DateTable[Date]) RETURN CALCULATE([Net Profit],DATESBETWEEN(DateTable[Date],"8/1/2022",seldate))
Then i made my current payout which isCurrent Payout = [Net Profit] * MAX(Investors[Percentage])
and finally i have the total payout which is :Total Payout = ([before] * MAX(Investors[Percentage])) + MAX(Investors[Initial Value])
here are some photos of my results :
for week 1ā:
for week 2:ā
all is left is for me to create a previous payout measure which i already know how to solve half of it i just need a similar calcualtion to the "before" measure but he seldate is 1 week before can anyone help?
please refresh the download link
- lbendlin4 years ago
Super User
your "Previous Payout" measure looks like it could use some cleanup. Since you are already connecting to the dates table you could write it like this
VAR _max =
MIN ( 'DateTable'[Date] )
VAR _min =
2* _max -MAX ( 'DateTable'[Date] )
and then use that to project that filter onto the Dates table.
VAR Totalrevenue =
CALCULATE (
SUM ( Revenue[Amount] ),
FILTER ( ALL (DateTable[Date]), DateTable[Date]>=_min && DateTable[Date]<=_max)
)
Here is the full measure.
Previous Payout = VAR _max = MIN ( 'DateTable'[Date] ) VAR _min = 2* _max -MAX ( 'DateTable'[Date] ) VAR Totalrevenue = CALCULATE ( SUM ( Revenue[Amount] ), FILTER ( ALL (DateTable[Date]), DateTable[Date]>=_min && DateTable[Date]<=_max) ) VAR Totalexpense = CALCULATE ( SUM ( Expenses[Amount] ), FILTER ( ALL (DateTable[Date]), DateTable[Date]>=_min && DateTable[Date]<=_max) ) VAR Grossprofit = ( Totalrevenue - Totalexpense ) VAR Taxes = ( Totalrevenue * 0.15 ) VAR Netprofit = Grossprofit - Taxes RETURN NetprofitHope this gets you closer to what you need.
Note that your Investors table is not connected to the data model.