I have a database of transactions that include start and end dates. I need to create a simple visualization with a date dimension that shows for each date, how many transactions started on that date, how many ended on that date, and what the backlog of transactions is, i.e., how many transactions have a start date less than the given date, but a end date greater than or equal to the given date.
This has me stumped, because it seems like a pretty simple ask, yet it looks like it would require an independent date range to compare the start date and end date fields to. I've tried adding the dates to the columns shelf with a dual axis, and use a count of transactions as the measure, but I can't figure out how to color them line graphs different from either other. And I can't for the life of me figure out how to get backlog.
Sample workbook attached.