Is there a way that I can create a variable to calculate the current balance for the previous loaded date? I have data for accounts for all business days and I what I want to do is calculate current balance - previous balance to get the variance. I created a variable to give me the max date and created one for the previous date.
vMaxDate = Max(Date) - for current balance
vPrvDate = Date(Max(Date) - 1) - for previous balance
The problem I am getting is for example on a Monday, the vPrvDate is giving me the day of Sunday and since I only have data for business days, the value that is giving me is 0. What I need for the vPrvDate variable is to yield the last date of loaded data, for example on friday.
Account
Balance
Date
XXXX1
50000
04/09/15
XXXX1
100000
04/10/15
XXXX1
150000
04/13/15
XXXX1
50000
04/15/15
What I need is that when I select Date 04/13/15:
It provides me with the Current Balance of $150,000 and previous balance of $100,000. Causing a positive difference of $50,000
Currenly with the vPrvDate as I have it, when I select Date 04/13/15
It provides me with the Current Balance of $150,000, previous balance of $0 from 04/12/15 (since I dont have data for that date) and a difference of $0.
