Calculated Column - Every day, based on Previous Day Value

[DataUser]

I'm looking to add/subtract two columns from present day with the value calculated on the previous day.  In Excel this is pretty simple but I can't do it there & input it in because one of my values that I want to add continuously is being calculated in the table. 

I have a fixed starting value. Then, I want to add Value A to it and subtract Value B. Value C is the finished value. For the next day, I want to replace that fixed starting value with the Value C from the previous day. Add a new value A and subtract a new value B and then I have a new Value C, and so forth.  Regardless of what the math is, the answer must be 300 or less than. So if it comes out more, I've been using "MIN" to replace it with 300.\

Any ideas for a formula? 

[v-zhangti · Accepted solution]

Hi, @DataUser 

Your problem can be solved by a few calculated columns.

Date2 = [Date]-1

N1 = [Fixed Initial Value]+[A]-[B]

N2 =
IF (
    CALCULATE (
        SUM ( 'Table'[N1] ),
        FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) )
    ) >= 300,
    300,
    CALCULATE (
        SUM ( 'Table'[N1] ),
        FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) )
    )
)

C = 
IF (
    CALCULATE (
        MAX ( 'Table'[N2] ),
        FILTER ( 'Table', [Date] = EARLIER ( 'Table'[Date2] ) )
    ) >= 300,
    300 + [A] - [B],
    [N2]
)

Best Regards,

Community Support Team _Charlotte

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

[DataUser]

Here is the data and what I want to do in Excel form - I'm not sure how to do this in PowerBI. At most, C should equal 300. If the math comes out with a higher value, I would want it to cap off at "300". For my formula in Excel, it's simple: =MIN(300,"whatever the C value is from the day before"+"A" -"B"). 

Date	Fixed Initial Value	A	B	C = This is what I want
1/1/2022	100	20	4	116
1/2/2022		80	3	193
1/3/2022		120	6	300
1/4/2022		40	60	280

[ryan_mayu]

@DataUser 

could you pls provide the sample data? 

[DataUser]

I need to somehow keep that inital fixed value in. If I do it this way and reference the previous value A and B, I lose the initial 100. Is there a way to do this and include it? 

[AntoineTRICHET]

Hi @DataUser 

The function LOOKUPVALUE DAX (https://docs.microsoft.com/fr-fr/dax/lookupvalue-function-dax) can help you I think to get the data from the previous days & use it in your measure.
VAR Value_A_from_previous_date =
LOOKUPVALUE([column A], [column date], [column date]-1)
VAR Value_B_from_previous_date =
LOOKUPVALUE([column B], [column date], [column date]-1)
RETURN
MIN(Value_A_from_previous_date - Value_B_from_previous_date + [column A] - [column B], 300)

Please accept it as a solution if it solved your issue.

Have a nice evening