How to add fixed amount

[Dimitris_Kats]

Hello dear members.

I need your help once more.

I have a fund report with inflows and outflows from 1/1/2016 until now.

I would like to calculate the ( inflows - outflows) day by day but I need to add at the 1/1/2019 a fixed amount (100.000).

So for example I want to calculate for 1/1/2019 the following:
(100000 +  inflows) - outflows.

Every day I want to add in the inflows the remaining amount of the previous day and subtract the outflows. 
The result I want to be added at the inflows of the 2/1/2019 etc. 

How can you achive that?

Thank you very much!

[FreemanZ]

hi @Dimitris_Kats 

try to create a measure like this:

Amount = 
VAR _mindate = MIN(TableName[Date])
VAR _maxdate = MAX(TableName[Date])
VAR _fixedamount = IF(_mindate<=DATE(2019,1,1)&& _maxdate >=DATE(2019,1,1), 100, 0)
RETURN
SUM(TableName[Inflows]) + _fixedamount - SUM(TableName[Outflows])

in case of issue, please consider provide some sample data and @me

[Dimitris_Kats]

Dear @FreemanZ 

Thank you very much for replying to me.

Let me give you some data

date	Inflow	Outflow
20/12/2018	150	130
21/12/2018	100	90
22/12/2018	150	140
23/12/2018	200	160
24/12/2018	160	160
25/12/2018	200	210
26/12/2018	110	100
27/12/2018	60	100
28/12/2018	180	150
29/12/2018	180	90
30/12/2018	120	60
31/12/2018	100	50
1/1/2019	150	100
2/1/2019	100	100
3/1/2019	180	100
4/1/2019	90	60
5/1/2019	100	50

The desired result for 1/1/2019 will be the result of inflows - outflows +Fixed amount

So for the 1/1/2019 the result will be 150 (inflows of 1/1/2019) - 100 (outflows of 1/1/2019) + 100 (fixed amount) = 150
For the 2/1/2019 the result will be 150 (previous day result) + 100 (inflows of 2)/1/2019- 100 (outflows of 2/1/2019) = 150
etc

How can achive that?

Thank you very much again for your help!!! 

[FreemanZ]

hi @Dimitris_Kats 

how do you expect to present your result?

[Dimitris_Kats]

Hello @FreemanZ 
Thank you very much for replying to me

I would like to have the results daily and monthly.

For example at the above table I would like to have another column with the result day by day

1/1/2019	150
2/1/2019	150
3/1/2019	230
4/1/2019	260
5/1/2019	310

[Anonymous]

Hi @Dimitris_Kats ,

You can try this code to create a calculated column in your table.

Column = 
VAR _Before_2019_1_1 =
    SUMX (
        FILTER ( 'Table', 'Table'[date] <= EARLIER ( 'Table'[date] ) ),
        [Inflow] - [Outflow]
    )
VAR _After_2019_1_1 =
    SUMX (
        FILTER (
            'Table',
            'Table'[date] <= EARLIER ( 'Table'[date] )
                && 'Table'[date] >= DATE ( 2019, 01, 01 )
        ),
        [Inflow] - [Outflow]
    ) + 100
RETURN
    IF ( 'Table'[date] >= DATE ( 2019, 01, 01 ), _After_2019_1_1, _Before_2019_1_1 )

Result is as below.

Best Regards,
Rico Zhou

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

[Dimitris_Kats · Accepted solution]

Hi @Anonymous 

Thank you very much for your solution.

To be honest i managed to solve it on my own based on the solution of @FreemanZ 
I calculated the 

Amount = 
VAR _mindate = MIN(TableName[Date])
VAR _maxdate = MAX(TableName[Date])
VAR _fixedamount = IF(_mindate<=DATE(2019,1,1)&& _maxdate >=DATE(2019,1,1), 100, 0)
RETURN
SUM(TableName[Inflows]) + _fixedamount - SUM(TableName[Outflows])

And then I calculated the running total 🙂
But thank you very much. Your help is greatly appreciated. 
Unfortynately I cannot creat a calculated column at my model 😞