Forum Discussion
Aligning Data With different dates
Hi Del235 ,
Thank you for the clarification. I was able to reproduce the scenario using a single dataset where both events are stored in the same table with a single Date column and Sales field.
In this approach, I first added a custom column in Power Query to create an aligned date using a 728-day offset for the older event data so that the weekdays match correctly between 2024 and 2026. After that, I pivoted the Event column using the Sales column as values and then performed a Group By operation on the aligned date to combine both event values into the same row.
This produced the expected output where both events align correctly by weekday on a single date axis.
I have attached the PBIX file for reference. Please review the Power Query steps and transformations in the file for better clarity on the implementation.
Please let us know if you need any further assistance.
For aligning 2024 to 2026 by day of week, just add 728 days (which is exactly 104 weeks) to each 2024 date. That offset accounts for the 2024 leap year and lands every 2024 weekday on the same weekday in 2026, so 5/3/2024 maps to 5/1/2026 (both Fridays) and the rest follow the same pattern.
In Power Query, in the 2024 table add a custom column:
AlignedDate = Date.AddDays([Date], 728)
Or as a DAX calculated column:
AlignedDate = [Date] + 728
Then merge the two tables on AlignedDate equal to the 2026 date (or build a relationship), put the 2026 date on the visual, and pull Sales1 from the 2026 table and Sales2 from the 2024 table.
If this solved your issue, please mark it as the accepted solution and give it a kudos.
Best,
Shai Karmani