The short answer is "morally, $Y$ should be the loop space of $X$," and we'll see how far this answer gets towards justifying that.

First some words that are not about spaces. My go-to example of two power series, both of which are the Hilbert / Poincare series of something, whose product is $1$ is

$$\left( \sum_{k \ge 0} t^k \dim S^k(V) \right) \left( \sum_{k \ge 0} (-1)^k t^k \dim \Lambda^k(V) \right) = 1$$

where $V$ is a finite-dimensional vector space. Explicitly, if $\dim V = d$ then $\dim S^k(V) = {d+k-1 \choose k}, \dim \Lambda^n(V) = {d \choose k}$, and we recover the power series identity

$$\frac{1}{(1 - t)^d} (1 - t)^d = 1.$$

Here we should think of the left factor as being the graded dimension of $S(V)$, where $V$ is regarded as being in degree $0$, and the right factor as being the graded dimension of $S(V[1])$, where the shift is the origin of the signs and the taking of exterior powers.

The identity above reflects the fact that the symmetric and exterior algebras are Koszul dual. It can be categorified to the fact that a certain double complex refining the Koszul complex has homology concentrated in one degree, and can be used to write down a nice condition implying that a graded ring is Koszul; see Lemma 2.11.1 and Theorem 2.11.1 of Beilinson, Ginzburg, and Soergel's Koszul Duality Patterns in Representation Theory for details.

Now some words about spaces. Below all cohomologies and homologies are with rational coefficients.

Topologically our starting point is the intuition that fibrations

$$F \to E \to B$$

are like "twisted products" $E \cong B \times F$. One manifestation of this idea is that, under sufficiently nice hypotheses, $H_{\bullet}(E)$ is something like $H_{\bullet}(B) \otimes H_{\bullet}(F)$ (maybe we want $B$ to be simply connected and the Serre spectral sequence to degenerate at the second page, or something like that). In particular, under sufficiently nice hypotheses, the Poincare series of $F, E, B$ should satisfy

$$p_E(t) = p_B(t) p_F(t).$$

Now suppose $E \cong \ast$ is contractible, so $p_E(t) = 1$ and the fibration above is (weakly homotopy equivalent to) the path space fibration

$$\Omega B \to PB \to B.$$

Then the identity $p_E(t) = p_B(t) p_F(t)$ means that $p_B(t)$ and $p_{\Omega B}(t)$ are inverse power series. Of course by definition both must have non-negative integer coefficients so this is quite silly. Nevertheless the structure of the second page of the Serre spectral sequence is suggestive, and in particular something like the following can be deduced from Theorem 3.27 in McCleary's A User's Guide to Spectral Sequences.

Theorem: (Hypotheses). Then:

1. If $H^{\bullet}(B)$ is an exterior algebra on generators $x_1, ..., x_m$ of odd degrees $2r_i - 1$, then $H^{\bullet}(\Omega B)$ is a polynomial algebra on generators $y_1, ..., y_m$ of even degrees $2r_i - 2$.

2. If $H^{\bullet}(\Omega B)$ is an exterior algebra on generators $x_1, ..., x_m$ of odd degrees $2r_i - 1$, then $H^{\bullet}(B)$ is a polynomial algebra on generators $y_1, ..., y_m$ of even degrees $2r_i$.

In particular, the Poincare series are almost inverses except for the signs and the degree shift. For an example of the first case let $B$ be a product of odd-dimensional spheres (not including circles), and for an example of the second case let $B$ be the classifying space of a compact connected Lie group $G$, so that $\Omega B \cong G$.

Regarding the relationship between the first and second set of words, I have been told that there is a sense in which cochains $C^{\bullet}(B)$ on a space and chains $C_{\bullet}(\Omega B)$ on its loop space are Koszul dual. Unfortunately I don't know a precise statement of this relationship. There should be a lot more to say here from the perspective of rational homotopy theory but I'm not the person to say it.