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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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