Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
)
Solved! Go to Solution.
Hi @mpenkin below is v2 version for calculated column as before.
Outuput as yours
Proud to be a 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!
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!
Hi, more details is needed , like your model, relationships...
Proud to be a 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
Proud to be a Super User!
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!
Hi @mpenkin below is v2 version for calculated column as before.
Outuput as yours
Proud to be a Super User!
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 |
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
21 | |
21 | |
13 | |
12 |
User | Count |
---|---|
43 | |
28 | |
25 | |
23 | |
22 |