Thanks for the comments on this, it is very valueable.
When i use this solution on my table, it sums all the values. In my situation i want to have the last value available for the 6 different months
I used in a calculated table the following calculated column: MaxDate = MAXX(RELATEDTABLE(TestBalances);TestBalances[Date])
The only problem here is that it takes the very last value, of a range of dates.
I want to have the last value of a month
date table sample: date yearmonthnumber 01-jan-2016 2016_01 02-jan-2016 2016_01 .. 31-jan-2016 2016_01 01-feb-2016 2016_02 ...
balance table sample: date balance type 01-jan-2016 2000 Green 15-jan-2016 2005 Green 26-jan-2016 2009 Green 01-feb-2016 2100 Green 15-feb-2016 2105 Green 24-feb-2016 2109 Green 01-mch-2016 2200 Green 15-mch-2016 2205 Green 17-mch-2016 2209 Green
The result that I want to have is: 26-jan-2016 2009 Green 24-feb-2016 2109 Green 17-mch-2016 2209 Green
Important note: when there is no value of the balance for a month, it should take the last available balance. Any suggestions? John