Good morning,
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.
Please let me know who I can fix this. Thank you.
