cancel
Showing results for
Did you mean:
Frequent Visitor

## In and out-flow from money. Create difference and aggregate value by month.

Dear Power Bi Users,

I am struggling with a little issue right now. I did some research in this forum but did not find the right solution. Maybe I am just on the wrong path, this is why I hope there might be a simple answer.

This is some sample data. I have a register of various accounts and money which went in and out on these accounts on a certain date. The date is transformed into a month format.

 Account Date Month Amount taken Amount received ZZZ Bank 21.02.2017 2017-02 10 AAA Bank 20.02.2017 2017-02 5 ZZZ Bank 01.02.2017 2017-02 7 ZZZ Bank 01.03.2017 2017-03 21

Now I would like to create a table/formula which can reproduce the information to a total balance each month. So how much money we have in total, thus adding previous month etc.

 ZZZ Bank AAA Bank Cash 2017-01 2017-02 -17 5 2017-03 21 2017-04 2017-05 2017-06 2017-07 2017-08 2017-09 2017-10 2017-11 2017-12

With the data I want to create a stacked chart (accounts and their values by month), which should be pretty easy with having the information.

I thought about a CALCUATE Formula, but somehow I do not get it running.

I appreciate any advice or tips.

Sven

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

## Re: n Re: In and out-flow from money. Create difference and aggregate value by month.

Hi @sven7,

You are using a column you should use a measure, columns are always calculated at row level, measures are calculated at context level so taking into account the different levels of information.

Change your column to a measure and it will work,

Regards,

MFelix

Regards

Miguel Felix

Proud to be a Datanaut!

22 REPLIES 22
Super User III

## Re: In and out-flow from money. Create difference and aggregate value by month.

Hi @sven7,

I would add a calendar table to you model but using your columns just add the following measure:

`Balance = SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken])`

Regards,

MFelix

Regards

Miguel Felix

Proud to be a Datanaut!

Frequent Visitor

## Re: In and out-flow from money. Create difference and aggregate value by month.

@MFelix thanks for your advice. I was also trying this approach but it is only a partly solution. It only gives me the difference per month, meaning if I generated a plus or minus in the month. I also would like to know what is my current cash balance. So there would need to be like aome kind of aggregate from the previous month to see the total available money by month. Do you maybe also have a good idea to this point?

Sven
Super User III

## Re: In and out-flow from money. Create difference and aggregate value by month.

Hi @sven7,

Add a calendar table and don't make any relationship between both tables.

```Balance =
var DimDate = MAX('Calendar'[Date])
return

Regards,

MFelix

Regards

Miguel Felix

Proud to be a Datanaut!

Frequent Visitor

## Re: In and out-flow from money. Create difference and aggregate value by month.

@MFelix Thanks again for your help.

I have tried this approach, but it only gives me the same value for all dates. I have created a seperated table with the calendarauto function, made sure there is no connection to other tables and put in your formula and adjusted it according to my names.

Do you have an idea why there is the same value everywhere?

Sven

Microsoft

## Re: In and out-flow from money. Create difference and aggregate value by month.

Hi @sven7,

How did you create the visuals? It seems this simple measure is enough. You can see it from the snapshot.

```Measure =
SUM ( Table1[Amount received] ) - SUM ( Table1[Amount taken] )```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: In and out-flow from money. Create difference and aggregate value by month.

thanks for your tips. I believe that my description was not accurate enough. I would like to see how the available cash balance increases or decreases by month and see the total current cash balance in the latest month. I have already created the graph in Excel, but I would like to create the formula in Power BI because the Excel dta is outdated.

Our different accounts shall be reflected in a stacked chart by the balance each month and aggregate until the latest month. I only imported the data sheet from Excel.

Thanks again for all your help in this community.

BR Sven

Super User III

## Re: In and out-flow from money. Create difference and aggregate value by month.

Hi @sven7,

What is the month column you have in your table is it from calendar or from you data table?

Regards,

MFelix

Regards

Miguel Felix

Proud to be a Datanaut!

Frequent Visitor

## n Re: In and out-flow from money. Create difference and aggregate value by month.

Hi @MFelix,

the month column in my table draws the month from the date a booking has been made on an account.

`Month = Format(Logs[Date];"yyyy-MM")`

This is how my calendar table looks like, there is no connection to the other sheet.

`calendar = CALENDARAUTO(12)`

BR Sven

Super User III

## Re: n Re: In and out-flow from money. Create difference and aggregate value by month.

hi @sven7,

Create a month column on your calendar table and place that column on your visual

Regards,

Mfelix

Regards

Miguel Felix

Proud to be a Datanaut!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!