Aligning Data With different dates

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

For your reference.

Step 0: I use these data below.

<Data set 1>

<Data set 2>

Step 1: I make a calendar table below.

Step 2: I add two relationships.

Step 3: I make a table.

Hi,

Try this approach

1. Ensure that the seconf colum is named the same in both tables, say it is anamed as Sales
2. In each table, create a third column with the name of th product in each row
3. In PQ, append the 2 tables.  Name this table as Data
4. Create a Calendar table
5. Create a *:1 relationship from the Date column of the appended table to the date column of the Calendar table
6. To your matrix visual, drag the Date column from the Calendar table
7. To your matrix visual, drag the Product column from the appended table
8. Write this measure: Measure = sum(Data[Sales])

Step 1) Create the Sales 2 measure

Sales 2 (Day Aligned) =
VAR _CurrentDate =
    MAX ( Dataset1[Date] )
VAR _DayOfWeek =
    WEEKDAY ( _CurrentDate, 2 )
VAR _WeekNumber =
    WEEKNUM ( _CurrentDate, 2 )
VAR _MatchingDate =
    CALCULATE (
        MAX ( Dataset2[Date] ),
        FILTER (
            ALL ( Dataset2 ),
            WEEKDAY ( Dataset2[Date], 2 ) = _DayOfWeek
                && WEEKNUM ( Dataset2[Date], 2 ) = _WeekNumber
        )
    )
RETURN
    CALCULATE (
        SUM ( Dataset2[Sales2] ),
        Dataset2[Date] = _MatchingDate
    )

Step 2) Configure the table visual

• Rows: Dataset1[Date]
• Values: Sales 1 measure and Sales 2 (Day Aligned) measure

Hi Del235 ,

I would take a moment to thank Shai_Karmani  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions