Disclaimer: I know there are several similar topics here, but neither of them has correct answer, so here goes another try.
Data is a typical sequential log file that represents status changes for a set of entities over time - similar to this:
| DateTime | Key | Status |
| 2022-10-22 7:03 | A | New |
| 2022-11-01 3:00 | B | New |
| 2022-11-02 11:01 | A | Approved |
| 2022-11-02 17:00 | A | Cancelled |
| 2022-11-03 8:00 | A | Revised |
| 2022-11-04 7:00 | B | Approved |
Notes (this is important since usually suggested solutions do not work as they use wrong assumptions):
- There may be more than one status update (row) per given key in the log table
- DateTime (timestamps of changes) appear at random intervals; it's not guarateed to have a row per each date from the calendar
What we want is to know key's current status as of any given date (EoD of this given date to be precise). For the example dataset above, this would be:
| Date | A | B |
| 2022-10-22 | New | (null) |
| 2022-10-23 | New | (null) |
| ... | ... | ... |
| 2022-11-01 | New | New |
| 2022-11-02 | Cancelled | New |
| 2022-11-03 | Revised | New |
| 2022-11-04 | Revised | Approved |
Can this be achieved with DAX measure?
