Need Help to obtain balance for a cashflow

[danielsouza]

I have this cashflow example:

I just want to show in a card report the last value from "Saldo final de caixa" ordered by last date, for example in 02/01/2017 the value for Saldo final de caixa is: 3148,81, and 09/01/2017 : 2569,85

Thanks a lot for the help

[MFelix]

Hi @danielsouza,

Looking at your data I would use something like the measure below, this will calculate the cumulative values for "entradas e Saidas" adding the "saldo" column when it doesn't have any description.

Saldo Final Caixa =
CALCULATE (
    CALCULATE ( SUM ( Table1[Saldo] ); Table1[Tipo] IN { "" } )
        + CALCULATE ( SUM ( Table1[Valor] ); Table1[Tipo] IN { "Entrada" } )
        - CALCULATE ( SUM ( Table1[Valor] ); Table1[Tipo] IN { "Saida" } );
    FILTER ( ALL ( Table1[Data] ); Table1[Data] <= MAX ( Table1[Data] ) )
)

However I would add the "Saldo Inicial" as a "Tipo" and change the measure to something as below.

Saldo Final Caixa =
CALCULATE (
    CALCULATE (
        SUM ( Table1[Valor] );
        Table1[Tipo] IN { "Entrada"; "Saldo Inicial" }
    )
        - CALCULATE ( SUM ( Table1[Valor] ); Table1[Tipo] IN { "Saida" } );
    FILTER ( ALL ( Table1[Data] ); Table1[Data] <= MAX ( Table1[Data] ) )
)

Since in the Filter we are using all the Data from your table this will alllow you to add slicers in your visuals and always get the right result since you are getting a cumulative total, and the ALL formula overlaps all of your other filters in your calculations.

Be aware that if you want to make a full Cash flow detail with Entradas and Saidas the formula needs to be adjusted, so that compreends all the columns you need just to present it in a card this should be enough.

Regards,

MFelix

[danielsouza]

Dear MFelix Thank you so much for helping me! But I need to return the value, for example, in the last insert for 02/01/2017 as Saldo Final de Caixa (Final Cash Balance) = R$3148,81, but the measure is returning Valor = R$1461,70 Sorry I'm beginner at this, but I can't see how to obtain the value of Saldo Final de Caixa column, only to plot the value, because is already calculated in the Excel, I tried to create an Index (Índice) column, maybe it helps, because we can have several inserts for a same date.

Thanks a lot for the help

Regards

Daniel

[MFelix]

Did you use.the.measure. I.refer ?

As a best practice.you.should.calculate this value not using it from a column... then apllying a filter on it returns youer.total.

MFelix

[danielsouza]

No, I haven't. By the way I can supress this column Saldo Final de Caixa and calculate its values using a measure.

But I'm not sure If I could plot in a card report, the last insert by date, containing Saldo Final de Caixa value.

I'll try tomorrow and tell you.

Many thanks

Daniel

[danielsouza]

No success.

Can you help me only to show a specific value in a card report?

When I use date filter, shows me the last value for Saldo final de caixa:

I made an example using text box:

Many thanks

Daniel

[MFelix · Accepted solution]

Hi @danielsouza,

As you can see below using a slicer for dates I'm abble to make the presentation on a card for the value of that day. In the image below the table as not interactions with the slicer in order for you to see that the values are matching.

See attach the PBIX I used.

Regards,

MFelix

[danielsouza]

Yes that's it!!!

Thanks a lot!!!

Now I gonna study and understand this measure.

Best regards

Daniel

[MFelix]

Hi @danielsouza,

As you can see below using a slicer for dates I'm abble to make the presentation on a card for the value of that day. In the image below the table as not interactions with the slicer in order for you to see that the values are matching.

See attach the PBIX I used.

[danielsouza]

This is a cashflow, I just want to show in a card report the most recent value by date:

 https://drive.google.com/open?id=1e3_dtR7GdbcNnVibpRVHH633Z3iX6-wV
                
                
                
                
                
                
                
                
                
                

 

Saldo final de caixa = balance, is already calculated, and I filter it in a report by date, for example 02/01/2017 the final value is R$3148,81.

 

Thanks for the attention.

[MFelix]

Hi @danielsouza,

Believe this question is duplicated with the one below check my answer on that particular question.

http://community.powerbi.com/t5/Desktop/Need-Help-to-obtain-balance-for-a-cashflow/m-p/396540

Regards,

MFelix

[danielsouza]

Thanks a lot MFelix, should I erase this post?