My question is: I would like to be able to use a Calendar (based from the Date dimension preferably) and select on any day and it show the balance.
- I select 6/3/16 on the calendar object and the balance shown is 73,017.956.
- I select 5/29/16 on the calendar object and the balance shown is 76,273.555 (from last balance at 5/27)
Metal report 1.qvw 1.9 MB
Try this code, it will give what might look like an odd data model and you can tidy it up if you want but it's good to leave it as it is so you can get to understand how the IntervalMatch function works.
Add a listbox for Date and a text box with the formula =Only(Balance) and play around with it:
LOAD * INLINE [
TempMinDate: //Get the start date for making a calendar
Min(Date) as StartDate
Let vStartDate = PEEK('StartDate',0,'TempMinDate');// start date held in this variable
DROP Table TempMinDate;
Date as From,
Date(RangeMin(Today(),Peek('Date',RecNo(),'Balances') -1)) as To,
//The above line peeks ahead one record in the Balances table to see the To Date and subtracts 1.
//If Peek does not return a value (when reading last record and there is no next record to peek) Today() is returned.
Drop Table Balances;
Calendar: // Make a simple calandar of dates from start to today
Date(TempDate) as Date;
LOAD IterNo() + $(vStartDate) -1 as TempDate
AutoGenerate 1 While IterNo() + $(vStartDate) -1 <= Today();
IntervalMatch(Date) LOAD From, To Resident Periods; // This matches each date in the calendar to a time period