I have a Data Table showing history of status changes for transactions. Each transaction has entries only on days that the status changed. Each record also includes the date that the transaction is closed.
A simple example:
| Status Change Date | Transaction Status | Next Close Date |
| 2022-01-30 | Created | 2022-02-01 |
| 2022-02-01 | Completed | null |
| 2022-02-02 | Reopen | null |
I would like to create a visualization shows that derives the following measures for each calendar day.
| Date | Open BOD | New | Reopen | Closed | Open EOD |
| 2022-01-30 | 0 | 1 | 0 | 0 | 1 |
| 2022-01-31 | 1 | 0 | 0 | 0 | 1 |
| 2022-02-01 | 1 | 0 | 0 | 1 | 0 |
| 2022-02-02 | 0 | 0 | 1 | 0 | 1 |
I can easily obtain the New, Reopen and Close values, but am struggling with the Open BOD and Open EOD values, since I need to derive values even if there isn't a change in status for the date, e.g. 2022-01-31 has BOD & EOD .
Open at Beginning of Day
Next Close Date is blank or Next Close Date >= date shown in the resulting table
Open at End of Day
Next Close Date is blank or Next Close Date > date shown in the resulting table
I was originally thinking of generating the date in the resulting table, but could just use the distinct list of Status Change Dates.
Can anyone help me figure out how to calculate these two sums?
