Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Icon for Helper V rankHelper 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 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!

  • 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 😞 

6 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

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

      Dear FreemanZ 

      Thank you very much for replying to me.

      Let me give you some data

      dateInflowOutflow

      20/12/2018

      		150130
      21/12/201810090
      22/12/2018150140
      23/12/2018200160
      24/12/2018160160
      25/12/2018200210
      26/12/2018110100
      27/12/201860100
      28/12/2018180150
      29/12/201818090
      30/12/201812060
      31/12/201810050
      1/1/2019150100
      2/1/2019100100
      3/1/2019180100
      4/1/20199060
      5/1/201910050

       

      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!!!