cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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)RETURNCALCULATE(    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!
1 ACCEPTED SOLUTION
Super User

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

Outuput as yours

T -1 value formula v2 =
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

Proud to be a Super User!

7 REPLIES 7
Super User

Zdravo @mpenkin

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

Proud to be a Super User!

New Member

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!

Super User

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

Proud to be a Super User!

Super User

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 =
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

Proud to be a Super User!

New Member

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!

Super User

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

Outuput as yours

T -1 value formula v2 =
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

Proud to be a Super User!

New Member

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