How does one relate two sheets (by date range) for use in a Pivot Table

Question

I have two sheets in an excel spreadsheet, both are populated from Power Query accessing data from a REST endpoint. Sheet one has the "detail items" with a [ResolutionDate], [ItemCategory] and [Cost] and sheet two has the "period data" with a [StartDate], [EndDate], and [PeriodName].

What I'm ultimately after is a listing of the sum of [Cost] both by the [PeriodName] as well as broken down inside the period by [ItemCategory]

I basically need the PeriodName on the sheet one based on the [StartDate] and [EndDate], but cannot figure out a method via the Pivot Table relationship to related that data.

I've attempted to create a new column on Sheet one, the detailed items, with a VLOOKUP formula (which did get the PeriodName as a column), but when creating the pivot table from that sheet it wouldn't include that column (guess it is related to that column not being part of the query data?)

I tried to add a column in the Power Query, but wasn't able to determine the method to do a lookup on the other Power Query table and abandoned that method.

Is there a more reliable or straight-forward method of putting that from those two sheets together for an easier pivot table experience?

Answer 1

The more straight-forward method is called static segmentation which you can read all about here

Do the period end dates overlap with the period start dates, or does a following period start "the day after" the end date of the previous period?