Below calculations will provide you the 1st day and last day value of each month.
If [date]=datetrunc('month',[date]) then [balance] end
If [date]=Dateadd('month',1,datetrunc('month',[date]))-1 then [balance] end
Test_Table_Calcs.twbx 101.4 KB
Your solutions solves the immediate problem I'm trying to address without using table calculations at all. Excellent.
But I would still be interested to know if there is a simple solution using table calculations. I put together this example for two reasons: it's a simpler version of a problem a colleague is facing on real data (so your solution handles that well), but also to experiment with some aspects of table calculations that I still find surprising even after using them for some time.
So I'm curious whether there is a simple way to get this effect with table calculations (I suspect there must be), even if the solution without table calculations works fine.