cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sven7 Frequent Visitor
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.

 

AccountDateMonthAmount takenAmount received
ZZZ Bank21.02.20172017-0210 
AAA Bank20.02.20172017-02 5
ZZZ Bank01.02.20172017-027 
ZZZ Bank01.03.20172017-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 BankAAA BankCash
2017-01   
2017-02-175 
2017-0321  
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. 

 

Thanks in advance.

 

Sven

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

22 REPLIES 22
Super User III
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])

then add it to your table.

 

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sven7 Frequent Visitor
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?

Thanks in advance and Br

Sven
Super User III
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.

 

Then add this measure:

 

Balance = 
var DimDate = MAX('Calendar'[Date])
return
 TOTALYTD(SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken]),Money_In_Out[Date]<= DimDate)

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sven7 Frequent Visitor
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?

 

Thanks in advance and BR

Sven

Microsoft v-jiascu-msft
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] )

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.
sven7 Frequent Visitor
Frequent Visitor

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

Hi @v-jiascu-msft

 

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. 

 

iamge.JPG

 

Thanks again for all your help in this community.

 

BR Sven

Super User III
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sven7 Frequent Visitor
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

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?

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 Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors