Show last data for each date

[EdisonTrent]

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

[EdisonTrent]

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?

[v-janeyg-msft]

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

[EdisonTrent]

I would like to reword my question:

What if I had 100 cities and new city can appear any time in the data?

[v-janeyg-msft]

Hi, @EdisonTrent 

Obviously not, it can't have the exact same result you want.
It's very troublesome, you need to create a date table, establish relationship, and then write measure...

and that table visual is not available, maybe it can be displayed with matrix visual. In short, it is very complicated, mainly because your needs are not suitable for calculation on powerbi. 

Best Regards,

Community Support Team _Janey

[v-janeyg-msft · Accepted solution]

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