Forum Discussion

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

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"). 

 

 

DateFixed Initial ValueABC = This is what I want
1/1/2022100204116
1/2/2022 803193
1/3/2022 1206300
1/4/2022 4060280