Calculate Month to Date Income from Year to Date Data

[bkjohn2016]

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?

 

[Matt Allington]

Just wrap it in an if statement. If it is the first month, then just return the ytd number.

 

[bkjohn2016]

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)

 

[Matt Allington]

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.

 

[bkjohn2016]

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.

 

[Matt Allington]

You can use an if statement to test for current month sales (and return blank) or read my article about this problem here. https://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/