Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Helper V
3 years ago
Solved

How to add fixed amount

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 ...
  • Dimitris_Kats's avatar
    Dimitris_Kats
    3 years ago

    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 😞 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Dimitris_Kats
Helper V
3 years ago

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 😞