Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
mpenkin
New Member

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

‎05-15-2024 04:14 AM

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 dayAccountT ValueT-1 Value
10.05.2024ABC1000
10.05.2024DEF3000
10.05.2024XYZ2500
14.05.2024ABC50100
14.05.2024DEF380300
14.05.2024XYZ210250
15.05.2024ABC15050
15.05.2024DEF420380
15.05.2024XYZ200210
16.05.2024ABC170150
16.05.2024DEF400420
16.05.2024XYZ230200

 

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!
Labels:
Message 1 of 8
542 Views
0
1 ACCEPTED SOLUTION
some_bih
Super User
Super User
In response to mpenkin

‎05-15-2024 10:57 PM

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
some_bih_0-1715839042723.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

Message 4 of 8
459 Views
0
7 REPLIES 7
some_bih
Super User
Super User

‎05-16-2024 03:06 PM

Zdravo @mpenkin 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Message 6 of 8
420 Views
0
mpenkin
New Member
In response to some_bih

‎05-17-2024 07:23 AM

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!

Message 7 of 8
369 Views
0
some_bih
Super User
Super User
In response to mpenkin

‎05-17-2024 07:47 AM

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Message 8 of 8
364 Views
0
some_bih
Super User
Super User

‎05-15-2024 06:14 AM

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
some_bih_0-1715778877032.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Message 2 of 8
504 Views
0
mpenkin
New Member
In response to some_bih

‎05-15-2024 10:03 AM

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 dayCash TypeAccount NameT ValueT-1 Value
10.05.2024CashABC1000
10.05.2024DepositABC100
10.05.2024CashXYZ2500
14.05.2024CashABC50100
14.05.2024DepositABC2010
14.05.2024CashXYZ210250
14.05.2024BorrowingXYZ-150
15.05.2024CashABC15050
15.05.2024DepositABC1520
15.05.2024CashXYZ200210
15.05.2024BorrowingXYZ-20-15
16.05.2024CashABC170150
16.05.2024DepositABC1815
16.05.2024CashXYZ230200
16.05.2024BorrowingXYZ-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!

 

Message 3 of 8
490 Views
0
some_bih
Super User
Super User
In response to mpenkin

‎05-15-2024 10:57 PM

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
some_bih_0-1715839042723.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Message 4 of 8
460 Views
0
mpenkin
New Member
In response to some_bih

‎05-16-2024 08:14 AM

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 dayDeal RefCash TypeAccount NameT ValueT-1 Value
10.05.2024 CashABC1000
10.05.2024DP1DepositABC100
10.05.2024 CashXYZ2500
14.05.2024 CashABC50100
14.05.2024DP1DepositABC2010
14.05.2024 CashXYZ210250
14.05.2024BW1BorrowingXYZ-150
15.05.2024 CashABC15050
15.05.2024DP1DepositABC1520
15.05.2024 CashXYZ200210
15.05.2024BW1BorrowingXYZ-20-15
16.05.2024 CashABC170150
16.05.2024DP1DepositABC1815
16.05.2024 CashXYZ230200
16.05.2024BW1BorrowingXYZ-10-20
17.05.2024 CashXYZ230200
17.05.2024BW2BorrowingABC-1000
17.05.2024DP2DepositXYZ500
Message 5 of 8
433 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All