Set up a calculation field that returns date that calculates the previous quarter date:
Let ( [ D = Day ( Date ) ;
M = Month ( date ) ;
y = Year ( date ) ];
date ( D ; M - 3 ; Y )
) // this calculation will work even when M = 1
Then set up a self join relationship that matches by PK and this calculated date field to PK and Date. That will give you a direct link to the record for the same account from the previous quarter.
You need adjust for day. 6/31 results 7/1. Using year-month field make it easier.
OP typoed on 6/31 and 8/30, should be 6/30 and 8/31.
User19752 has a good point. Somehow I was thinking the first of the month, not the last day of the month.
Assuming that the date is always on the last day of the month...
Let ( [ dt = Date + 1;
D = Day ( Dt ) ;
M = Month ( dt ) ;
y = Year ( dt ) ];
date ( D ; M - 3 ; Y ) - 1
This calculates 3 months back for the next day, the first of the next month, then backs up one day to land back on the last day of the month.
1 of 1 people found this helpful
wouldn't the last day of any month be:
Date ( M+1 ; 0 ; Y )
day '0' of the next month is the last day of the given month (accounts for leaping days and years automatically & crossing year boundaries)
Date ( 13 ; 0 ; 2016 ) // 31 DEC 2016
Date ( 1 ; 0 ; 2016 ) // 31 DEC 2015
All this time I've been using Date ( M + 1 ; 1 ; Y ) - 1 to get the first last of the month... you just saved me 2 keystrokes!!!
Yes, use it all the time. I didn't do that here simply because the data shown already has the last day of the month, but I'd certainly include that detail if the original data isn't always the last day of the month.