Show balance by month - Power BI

1
votes

I have data about bank transactions. For example, I have two attributes "Posting date" and "Value". I want to see bank balance by each month. Problem is, that if I create measure and then add to graph (by months) I get SUM by each month in graph, but this is not balance.

For example, if data:

  • 01.01.2019 (500€)
  • 02.01.2019 (100€)
  • 24.01.2019 (-50€)
  • 25.01.2019 (-200€)
  • 04.02.2019 (100€)
  • 15.02.2019 (-50€)
  • 03.03.2019 (200€)

I need that kind of result:

  • January (350€)
  • February (400€)
  • March (600€)

But I get:

  • January (350€)
  • February (50€)
  • March (200€)
1
powerbidax
You need to define more precisely what "balance" means. Is it a cumulative sum from the beginning of data? or a cumulative sum from the start of the year? or rolling amount over some period of time? – RADO
@RADO i mean cumulative sum from beginning of data. – Andrej Hafner
Do you have a calendar table in your data model? – RADO
@RADO, no I don't have calendar table, I have only "Posting date" (Datetime) value in dataset. – Andrej Hafner

1 Answers

0
votes

I found solution: 

Balance = 
CALCULATE (
    SUM ( Dataset[Value] ); 
    FILTER (
        ALL ( Dataset);
        Dataset[Posting Date] <= MAX (Dataset[Posting Date] )
    )
)