I have two tables in Tableau: (A) one is a list of dates from the current day to 400 days prior and the other (B) is a list of ticket IDs along with open and closed dates for each ticket.
What I'm trying to do is determine the number of tickets that were active on each date in the date table. For each record in table A, I need the number of tickets that were opened on or before that date and were closed on or after that date.
In Excel, I would use COUNTIFS. In PowerPivot/Power BI, I would use a combination of COUNTROWS on table B with an EARLIER reference to table A. I'm pretty new to Tableau, so I'm thinking that I'm getting stuck trying to do something similar to Excel or DAX when Tableau probably has its own way.
I'm completely stumped. I tried finding an equivalent of what I know from other tools, but there doesn't seem to be one; and I'm sure that getting hung up on how Excel works here.
Note - The following answer describes an approach for when you only have the table of tickets (your table B) If you have a calendar table (your table A) then the approach by @Muff works assuming you properly cross-join your calendar table with your ticket table
--
Sometimes the easiest approach is to reshape your data prior to analysis. Tableau Desktop and Tableau Prep Builder allow you to automatically perform several steps such as Unions, Pivots, Splits automatically - meaning that you don't have to edit your data to have the effect of reshaping and simplifying it.
This answer describes one good way to address your situation. It was written several years ago before Tableau automatically supporting the Union operator, so the answer describes the steps using custom SQL.
Nowadays, you can get the same effect more easily just using Tableau's Union feature.
Here is the more in depth, but just dated, answer