Kennethy86
New Member
- Joined
- Jul 27, 2018
- Messages
- 3
[TABLE="width: 160"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]Start of Cash Flow
7/1/2019[/TD]
[TD="align: right"]Monthly cash flow
5000[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2019[/TD]
[TD="align: right"]5000[/TD]
[/TR]
</tbody>[/TABLE]
The above is my table in Power Pivot, I also have a date table expanding out for the time period I need.
I am currently using this formula to aggregate all of the monthly cash flows after the start date.
=CALCULATE (
SUM( FinancialInput[Equip Lease] ),
FILTER (
FinancialInput,
FinancialInput[Go date Expected] <= CALCULATE ( MAX ( 'Calendar'[date]))))
First, I need to modify the measure to only aggregate each months cash flows, if that particular month is after the start date but less than 5 years from the start date.
Next, I need an entirely separate measure that will increase the 5,000 cash flow by 5%/year compounded and also aggregate based on the same date criteria as mentioned above.
These 2 measures will get me very close to finishing my model. Thanks in advance for your help!
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]Start of Cash Flow
7/1/2019[/TD]
[TD="align: right"]Monthly cash flow
5000[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2019[/TD]
[TD="align: right"]5000[/TD]
[/TR]
</tbody>[/TABLE]
The above is my table in Power Pivot, I also have a date table expanding out for the time period I need.
I am currently using this formula to aggregate all of the monthly cash flows after the start date.
=CALCULATE (
SUM( FinancialInput[Equip Lease] ),
FILTER (
FinancialInput,
FinancialInput[Go date Expected] <= CALCULATE ( MAX ( 'Calendar'[date]))))
First, I need to modify the measure to only aggregate each months cash flows, if that particular month is after the start date but less than 5 years from the start date.
Next, I need an entirely separate measure that will increase the 5,000 cash flow by 5%/year compounded and also aggregate based on the same date criteria as mentioned above.
These 2 measures will get me very close to finishing my model. Thanks in advance for your help!