Calculate value of the previous available day for each data item (multiple items per date)

[mpenkin]

Hi everyone,

I have been looking for a solution that would look up the table by the name of an account and create a column with their values for the previous available reporting date (T-1). This would enable to track the change between every T-day and T-1 day.

Report day	Account	T Value	T-1 Value
10.05.2024	ABC	100	0
10.05.2024	DEF	300	0
10.05.2024	XYZ	250	0
14.05.2024	ABC	50	100
14.05.2024	DEF	380	300
14.05.2024	XYZ	210	250
15.05.2024	ABC	150	50
15.05.2024	DEF	420	380
15.05.2024	XYZ	200	210
16.05.2024	ABC	170	150
16.05.2024	DEF	400	420
16.05.2024	XYZ	230	200

I tried to use the following measure but it will be always looking into the second to last day inputted regardless of the slicer selection:

T-1 Value =
VAR MaxDate = CALCULATE(MAX('Table'[Report Day]), ALL('Table'[Report Day]))
VAR PreviousDate = CALCULATE(MAX('Table'[Report Day]), 'Table'[Report Day] < MaxDate)
RETURN
CALCULATE(
    SUM('Table'[Value]), 'Table'[Report Day] = PreviousDate
)

Would you have any suggestions for a formula that would look into the previous available date for every data line?

If a new account appears the T-1 should naturally be 0.

Thank you!

[some_bih]

Zdravo @mpenkin 

It is not clear based on table overview that acvount ABC is deactivated or deposit is matured.

[mpenkin]

Hi @some_bih,

I presume, additional data points would be required for that?

Could you let me know if whatever does not find the match can be calculated separately in another measure?

Thank you!

[some_bih]

Hi, more details is needed , like your model, relationships...

[some_bih]

Hi @mpenkin you want to get values in column T-1 Value for previous date per given Account?

If yes, please create calculate column as in example below 

T -1 value formula =

--adjust your Table name

VAR _currentRowDate = 'Table'[Report day]

VAR _current_account='Table'[Account]

VAR _previousRowDate =

    CALCULATE (

        MAX ( 'Table'[Report day] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Report day] < _currentRowDate && 'Table'[Account]=_current_account)

    )

VAR _result=

    IF (

        NOT ISBLANK ( _previousRowDate ),

        CALCULATE (

            MAX ( 'Table'[T_Value] ),

            FILTER ( ALL ('Table' ), 'Table'[Report day] = _previousRowDate && 'Table'[Account]=_current_account)

        )

    )

RETURN _result

 Output

[mpenkin]

Hi, @some_bih 

Thank you very much for the solution. I think we are on the right track.

I have realized something when I was trying to verify the values - some of the account names can repeat if there are different Cash Types (see the table below). Would you mind letting me know how to segregate the T-1 value based on both the Account Name and Cash Type data points?

Report day	Cash Type	Account Name	T Value	T-1 Value
10.05.2024	Cash	ABC	100	0
10.05.2024	Deposit	ABC	10	0
10.05.2024	Cash	XYZ	250	0
14.05.2024	Cash	ABC	50	100
14.05.2024	Deposit	ABC	20	10
14.05.2024	Cash	XYZ	210	250
14.05.2024	Borrowing	XYZ	-15	0
15.05.2024	Cash	ABC	150	50
15.05.2024	Deposit	ABC	15	20
15.05.2024	Cash	XYZ	200	210
15.05.2024	Borrowing	XYZ	-20	-15
16.05.2024	Cash	ABC	170	150
16.05.2024	Deposit	ABC	18	15
16.05.2024	Cash	XYZ	230	200
16.05.2024	Borrowing	XYZ	-10	-20

And one more question: As your measure creates a column, I was not able to subtract the T value from T-1 value which would show the difference - should I create a column copying the T value to enable that?

Thank you!

[some_bih · Accepted solution]

Hi @mpenkin below is v2 version for calculated column as before.

Outuput as yours

T -1 value formula v2 =

--adjust your Table name

VAR _currentRowDate = 'Table'[Report day]

VAR _current_account='Table'[Account Name]

VAR _current_cashtype='Table'[Cash Type]

VAR _previousRowDate =

    CALCULATE (

        MAX ( 'Table'[Report day] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Report day] < _currentRowDate && 'Table'[Account Name]=_current_account && 'Table'[Cash Type]=_current_cashtype)

    )

VAR _result=

    IF (

        NOT ISBLANK ( _previousRowDate ),

        CALCULATE (

            MAX ( 'Table'[T Value] ),

            FILTER ( ALL ('Table' ), 'Table'[Report day] = _previousRowDate && 'Table'[Account Name]=_current_account && 'Table'[Cash Type]=_current_cashtype)

        )

    )

RETURN _result

Output

[mpenkin]

Zdravo @some_bih,

Thank you for your swift reply! I have been testing your solution this whole morning to validate the data. It successfully matches values for the previous day with the items on the next day based on the name of the Account Name, Cash Types.

There is however one weak spot - if a certain item exhisted on T-1 and is not present on T-day (say an account was deactivated or a deposit has matured), the measure gets confused and disregards the missing items.

I wonder if the formula can be updated to mark the T-1 value even for the missing items on a T-day or calculated separately as amounts that did not find their matches.

Please see below an update sample where I also introduced the Deal Reference to track the deposits and Borrowings. Let's say that on 17.05.2024:

- Account ABC is deactivated so the T Value should be 0 and T-1 Value would be 170

- Deposit DP1 of ABC has matured (T Value = 0, T-1 Value =18)

- Borrowing BW1 for XYZ has been settled (T Value = 0, T-1 Value =-10)

Thank you very much for your efforts because for the existing items formula is just spot on!

Report day	Deal Ref	Cash Type	Account Name	T Value	T-1 Value
10.05.2024		Cash	ABC	100	0
10.05.2024	DP1	Deposit	ABC	10	0
10.05.2024		Cash	XYZ	250	0
14.05.2024		Cash	ABC	50	100
14.05.2024	DP1	Deposit	ABC	20	10
14.05.2024		Cash	XYZ	210	250
14.05.2024	BW1	Borrowing	XYZ	-15	0
15.05.2024		Cash	ABC	150	50
15.05.2024	DP1	Deposit	ABC	15	20
15.05.2024		Cash	XYZ	200	210
15.05.2024	BW1	Borrowing	XYZ	-20	-15
16.05.2024		Cash	ABC	170	150
16.05.2024	DP1	Deposit	ABC	18	15
16.05.2024		Cash	XYZ	230	200
16.05.2024	BW1	Borrowing	XYZ	-10	-20
17.05.2024		Cash	XYZ	230	200
17.05.2024	BW2	Borrowing	ABC	-100	0
17.05.2024	DP2	Deposit	XYZ	50	0