# Calculate Month to Date Income from Year to Date Data

#### bkjohn2016

##### New Member
I have two years of income data and unfortunately, my data source does not have the month to date data I need. I've resorted to calculating the month to date income with the following formulas:

Prior Month Interest Paid:=CALCULATE(SUM([Curr YTD Interest Paid]),PREVIOUSMONTH(DimDate[Date]))

Int Paid:=[Sum of Curr YTD Interest Paid 4]-[Prior Month Interest Paid]

However, in the first month of the second year the calculation results in a negative balance because month 12 has accumulated a full year of income. Any ideas?

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Just wrap it in an if statement. If it is the first month, then just return the ytd number.

Matt,

First I'd like to thank you for your response...I've seen many of your postings and understand that you are very talented. I might need further instruction though. IF statements don't work well with the CALCULATE function. I've tried to build the logic as I understand it in excel...but it doesn't work. I've tried to use the CALCULATE function with filters and I keep getting error messages. I went to the powerpivotpro website and found something that seemed accommodating....but returned a scalar value and would not work with my model. Simply stated, I would like to do this:

IF(MONTH NUMBER=1,YTD INT INCOME,YTD INT INCOME - PREVIOUS MONTH YTD INCOME)

OK, try this.

First you need to be able to identify in your calendar table which month is month 1 for each year. Assuming you have that, you can write a formula like this.

=if(average(calendar[MonthNumber])=1,[Sum of Curr YTD Interest Paid 4],[Sum of Curr YTD Interest Paid 4]-[Prior Month Interest Paid])

Let me explain the portion average(calendar[MonthNumber])=1

You can't write this if(
calendar[MonthNumber]=1... as you can't use a naked column in your expression. Assuming calendar[MonthNumber] has a single value (which it should if you have filter on month) then Average(calendar[MonthNumber]) will return the same as calendar[MonthNumber] and it is not a naked column.

That worked perfectly! Thank you so much...I've been wrestling with that for over a week. Now, how do I get it to stop calculating? My data is current through Oct 2016 but it's calculating a negative YTD balance in Nov 2016.

Replies
1
Views
777
Replies
1
Views
344
Replies
0
Views
991
Replies
0
Views
587
Replies
1
Views
206

1,203,327
Messages
6,054,751
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back