cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eliasayy
Impactful Individual
Impactful Individual

Incremental Values over period of time from calculated Measures

hello everyone, i am trying to build a table in pbix where it shows me payout of investors over a course of periods from my selected date, but i only have calculated measures for this case 

you may find my pbix file here:
https://1drv.ms/u/s!Ag9tIyk2ofNRjkTpg8L4VKb3_Rzw?e=EBa9Wn

and a glimpse of my table how i want the design to be:

powerbi.png

 

 and below, you can find my desired results and a walkthrough of how i want it calculated:

for the inital week (when investment was made) :

init.png


and then for  selected period after( in my case week 2):

week.png


@tamerj1 @amitchandak @daXtreme @Jihwan_Kim @johnt75 and of course anyone who has an idea please help

1 ACCEPTED SOLUTION
eliasayy
Impactful Individual
Impactful Individual

i finally found a solution for me and i will guide you through the steps :

first i created a  net profit measure 

 

 

 

Net 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 is 

 

 

 

Current 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 :

week 1.png

for week 2:

week 2.png


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?

Edit: i found a way to get previous payout
first i find the pre before:

pre before = 
VAR _min =
    MIN ( 'DateTable'[Date] )
VAR _Max =
    MAX ( 'DateTable'[Date] )
VAR _days =
    COUNTROWS ( CALENDAR ( _min, _Max ) )
VAR seldate = CALCULATE(MAX(DateTable[Date]),FILTER ( ALL ( DateTable ), [Date] <= _Max - _days && [Date] >= _min - _days ))
RETURN
CALCULATE([Net Profit],DATESBETWEEN(DateTable[Date],"8/1/2022",seldate))

then previous payout is:

pre Payout = ([pre before] * MAX(Investors[Percentage])) + MAX(Investors[Initial Value])













View solution in original post

1 REPLY 1
eliasayy
Impactful Individual
Impactful Individual

i finally found a solution for me and i will guide you through the steps :

first i created a  net profit measure 

 

 

 

Net 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 is 

 

 

 

Current 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 :

week 1.png

for week 2:

week 2.png


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?

Edit: i found a way to get previous payout
first i find the pre before:

pre before = 
VAR _min =
    MIN ( 'DateTable'[Date] )
VAR _Max =
    MAX ( 'DateTable'[Date] )
VAR _days =
    COUNTROWS ( CALENDAR ( _min, _Max ) )
VAR seldate = CALCULATE(MAX(DateTable[Date]),FILTER ( ALL ( DateTable ), [Date] <= _Max - _days && [Date] >= _min - _days ))
RETURN
CALCULATE([Net Profit],DATESBETWEEN(DateTable[Date],"8/1/2022",seldate))

then previous payout is:

pre Payout = ([pre before] * MAX(Investors[Percentage])) + MAX(Investors[Initial Value])













Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors