Forum Discussion

DataUser's avatar
DataUser
Icon for Helper I rankHelper I
4 years ago
Solved

Calculated Column - Every day, based on Previous Day Value

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 ...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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's avatar
DataUser
Icon for Helper I rankHelper I
4 years ago

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?