Forum Discussion

EdisonTrent's avatar
EdisonTrent
New Member
4 years ago
Solved

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 be...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

     

     

EdisonTrent's avatar
EdisonTrent
New Member
4 years ago

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's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    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's avatar
      EdisonTrent
      New Member
      4 years ago

      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's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        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