Hey everyone! First post, but I've used so many of the other threads to get to where I am currently and really appreciate this community!
For the below table, I'm trying to write a measure titled CURRENT DATE BALANCE that returns only the rows where the row date is the MAX date in the table. Similarly, I want a second measure for PRIOR DATE BALANCE that returns each row where the row date doesn't equal the table MAX date but only the row with the latest date for duplicate IDs.
| ID | DATE | BALANCE |
| DOC 1 | 9/22/2022 | $0.00 |
| DOC 2 | 9/22/2022 | $2,500.00 |
| DOC 3 | 9/22/2022 | $9,745.51 |
| DOC 1 | 9/23/2022 | $0.00 |
| DOC 2 | 9/23/2022 | $2,500.00 |
| DOC 3 | 9/23/2022 | $9,745.51 |
| DOC 1 | 9/26/2022 | $1,121.89 |
| DOC 4 | 9/26/2022 | $0.00 |
| DOC 3 | 9/26/2022 | $9,745.51 |
| DOC 1 | 9/27/2022 | $2,571.52 |
| DOC 3 | 9/27/2022 | $9,745.51 |
So from the above table, I'm hoping to get to a solution that looks like the below where rows with a date of 9/27 (max date) show balances under CURRENT DATE and each row with a date less than 9/27, but only the most recently dated row for duplicate IDs, shows under PRIOR DATE. (Date column included below for easier tracking from original table to desired solution)
| ID | CURRENT DATE BALANCE | PRIOR DATE BALANCE | DATE |
| DOC 1 | $2,571.52 | | 9/27/2022 |
| DOC 3 | $9,745.51 | | 9/27/2022 |
| DOC 2 | | $2,500.00 | 9/23/2022 |
| DOC 4 | | $0.00 | 9/26/2022 |
Below is the current formula I have in my measure, but this is returning all values under CURRENT DATE BALANCE as opposed to what I had hoped for above. I understand why it's doing this now, but I don't know how to fix it.
CURRENT DATE BALANCE = VAR charge_date = MAX(TABLE1[DATE]) RETURN CALCULATE(SUM(TABLE1[BALANCE]), TABLE1[DATE] = charge_date)
Thanks!
