Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sven7
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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

22 REPLIES 22
MFelix
Super User
Super User

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

@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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

@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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

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

hi @sven7,

 

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

 

Regards,

Mfelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

Hi @MFelix,

 

I used both ways, I am so far that it shows the balance or difference by each month, but not the rolling value. Also it is only showing 2017 and no previous years. 

 

Any further tips are much appreciated thank you so much for your help.

 

sample.JPGsample2.JPG

Hi @sven7,

 

My error mixup two different solutions I was working on.

 

Create the relationship between both tables and then add this measure:

Balance = 
 TOTALYTD(SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken]),'Calendar'[Date])

The other formula  without links was to make it month on month no matter the Year so the balence from december would go to January

In that case you need to use the formula:

Balance_1 = 
var dimdate = MAX('Calendar'[Date]) 
return
CALCULATE(SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken]),Money_In_Out[Date] <= dimdate)

and don't connet the tables.

 

Regards,

MFelix

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

Thanks @MFelix, it is getting closer. 

 

I can see the difference for each month now, but still there is no rolling value. When I try to use the month column from the calendar table it gives me an error that it cannot be displayed. 

 

Thanks again.


BR Sven

If you are using the first measure YTD create a relationship between the two tables.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

@MFelix This also works now with the connection to the other table and using the balance formula. However, it gives me the same picture again that it does not show the rolling value. I could only link the date between the tables not the month, following error:

21.JPG

 

Thanks Sven

You must link the date columns but use the.month colum from calendar table on your.visual

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

Yes, I tried that but that does not work. It only shows me the difference by month and no rolling value.

 

I also tried this from 

http://www.daxpatterns.com/cumulative-total/

 

but same graph is shown. Can it be because of my calendar or any other formatting issues maybe?

 

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

The auto calendar gives you the a calendar based on your minimum.and maximim date on your model in order for the YTD to.work you need to have a calendar starting in january.and ending un december I would add the calendar like this.

Calendar =
CALENDAR (
DATE ( YEAR ( MIN ( Transactions[Date] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( Transactions[Date] ) ), 12, 31 )
)

Then try the TOTALYTD formula

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

@MFelix Thank you so much for help, I really appreciate your help. Unfortunately still the same issue. I have got the YTD formula, I have set the calendar, I have linked the dates from the logs file and calendar and as the visual I have selected the dates from the calendar.

 

 

Hi @sven7,

 

I have used your data and produced this two models below one with the YTD values and another with the calculated values as you can see both make the same result. so total YTD values sum up to total year, on the second table the rollup goes trough 2018.

 

Can you send a print with the measures and table to check the values please.

money.png

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

@MFelix Thank you for your patience. I do not know where I make my mistake, I have used just some simple data to reproduce your table, but still the same issue. Please see here. 

 

Hope we can find my mistake. BR Sven

 

samplef.JPGsampled.JPGsamplee.JPGsampleg.JPG

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



sven7
Frequent Visitor

@MFelix What a stupid mistake... All working now, thank you very much. 

 

It really helps to visualize our data. Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.