Forum Discussion
Show last data for each date
Hello,
I receive daily bank balances only if the bank balance changes (see the example)
I would like to show the graph and table for each day in the year showing the last bank balance (see below)
At this moment, if there are data only for one bank account, graph with total balance shows only balance on this account.
I spent hours to find solution online and would really appreciate your help.
Thank you
| Date | City | Bank balance |
| 01.01.2022 | New York | 47 |
| 01.01.2022 | London | 32 |
| 04.01.2022 | London | 51 |
| 06.01.2022 | New York | 36 |
| 06.01.2022 | London | 78 |
| 08.01.2022 | New York | 28 |
| 09.01.2022 | London | 65 |
| 11.01.2022 | New York | 89 |
| 11.01.2022 | London | 25 |
| 12.01.2022 | London | 34 |
| 14.01.2022 | New York | 61 |
| 14.01.2022 | London | 3 |
| 17.01.2022 | New York | 55 |
| 17.01.2022 | London | 38 |
| 19.01.2022 | London | 58 |
| 20.01.2022 | New York | 90 |
Hi, EdisonTrent
First you need to pivot column like this:
Then you can create a new table and then create two columns to display what you want.
like this:
new table:
Table 2 = VAR Datelist = CALENDAR ( MIN ( 'Table'[Datefact] ), MAX ( 'Table'[Datefact] ) ) RETURN ADDCOLUMNS ( Datelist, "New York", VAR a = MAXX ( FILTER ( ALL ( 'Table' ), [Datefact] <= [Date] && [New York] <> BLANK () ), [Datefact] ) RETURN MAXX ( FILTER ( ALL ( 'Table' ), [Datefact] = a ), [New York] ), "London", VAR a = MAXX ( FILTER ( ALL ( 'Table' ), [Datefact] <= [Date] && [London] <> BLANK () ), [Datefact] ) RETURN MAXX ( FILTER ( ALL ( 'Table' ), [Datefact] = a ), [London] ) )new column:
Total = VAR CurNewYork = [New York] VAR CurLondon = [London] VAR LastNewYork = MAXX ( FILTER ( ALL ( 'Table 2' ), [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [New York] ) VAR LastLondon = MAXX ( FILTER ( 'Table 2', [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [London] ) RETURN SWITCH ( TRUE (), LastNewYork = BLANK () || ( LastNewYork = [New York] && LastLondon = [London] ) || LastNewYork <> [New York] && LastLondon <> [London], [New York] + [London], LastNewYork <> [New York] && LastLondon = [London], [New York] + LastLondon, LastNewYork = [New York] && LastLondon <> [London], LastNewYork + [London] )Total2 = VAR CurNewYork = [New York] VAR CurLondon = [London] VAR LastNewYork = MAXX ( FILTER ( ALL ( 'Table 2' ), [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [New York] ) VAR LastLondon = MAXX ( FILTER ( 'Table 2', [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [London] ) RETURN SWITCH ( TRUE (), LastNewYork = BLANK () || LastNewYork <> [New York] && LastLondon <> [London], [New York] + [London], LastNewYork <> [New York] && LastLondon = [London], [New York], LastNewYork = [New York] && LastLondon <> [London], [London] )Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _Janey
5 Replies
Hi, EdisonTrent
First you need to pivot column like this:
Then you can create a new table and then create two columns to display what you want.
like this:
new table:
Table 2 = VAR Datelist = CALENDAR ( MIN ( 'Table'[Datefact] ), MAX ( 'Table'[Datefact] ) ) RETURN ADDCOLUMNS ( Datelist, "New York", VAR a = MAXX ( FILTER ( ALL ( 'Table' ), [Datefact] <= [Date] && [New York] <> BLANK () ), [Datefact] ) RETURN MAXX ( FILTER ( ALL ( 'Table' ), [Datefact] = a ), [New York] ), "London", VAR a = MAXX ( FILTER ( ALL ( 'Table' ), [Datefact] <= [Date] && [London] <> BLANK () ), [Datefact] ) RETURN MAXX ( FILTER ( ALL ( 'Table' ), [Datefact] = a ), [London] ) )new column:
Total = VAR CurNewYork = [New York] VAR CurLondon = [London] VAR LastNewYork = MAXX ( FILTER ( ALL ( 'Table 2' ), [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [New York] ) VAR LastLondon = MAXX ( FILTER ( 'Table 2', [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [London] ) RETURN SWITCH ( TRUE (), LastNewYork = BLANK () || ( LastNewYork = [New York] && LastLondon = [London] ) || LastNewYork <> [New York] && LastLondon <> [London], [New York] + [London], LastNewYork <> [New York] && LastLondon = [London], [New York] + LastLondon, LastNewYork = [New York] && LastLondon <> [London], LastNewYork + [London] )Total2 = VAR CurNewYork = [New York] VAR CurLondon = [London] VAR LastNewYork = MAXX ( FILTER ( ALL ( 'Table 2' ), [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [New York] ) VAR LastLondon = MAXX ( FILTER ( 'Table 2', [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [London] ) RETURN SWITCH ( TRUE (), LastNewYork = BLANK () || LastNewYork <> [New York] && LastLondon <> [London], [New York] + [London], LastNewYork <> [New York] && LastLondon = [London], [New York], LastNewYork = [New York] && LastLondon <> [London], [London] )Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _Janey
Thank you very much for your help.
I wanted to ask you. How would you solve it if you had for example one hundred bank accounts and a new account can appear anytime in the future?
Hi, EdisonTrent
"If you had for example one hundred bank accounts and a new account can appear anytime in the future?"
I don't quite understand what it means. Since the calculation logic of your requirements is very complex, if your city values are only New York and London, then it is no problem to update the data normally, but if you want to add other cities, then the corresponding code should also be updated.
Best Regards,
Community Support Team _Janey
I would like to reword my question:
What if I had 100 cities and new city can appear any time in the data?
