Okay, I was able to OCR your screenshot to get the data in. Here are the results with the calculated column calculated via:
Campaign Date Offset =
DATEDIFF(
Data[Campaign Start Date],
Data[Date],
DAY
)
I would characterize this solution as “quasi–dynamic”. I created a disconnected table to hold the Campaign IDs and the Campaign Start Dates. You can update the campaign start dates in this table in Power Query and when you close and apply, it merges these dates with the main data based on Campaign ID.
I hope this is helpful. Full solution file attached below.
- Brian
eDNA Forum - Campaign Date Offsets.pbix (88.0 KB)
Campaign Dates.xlsx (10.5 KB)