Aligning Data With different dates

[Del235]

I have sales data from two different years that I want to align and show on a single table by day of the week.  For example:

Data set 1

Date                 Sales1

Fri  5/1/2026    1582

Sat 5/2/2026    538

Sun 5/3/2026    593

Data set 2

Date                 Sales2

Fri 5/3/2024      556

Sat 5/4/2024     840

Sun 5/5/20204  744

End Result

Date                 Sales 1      Sales 2

Fri 5/1/2026     1582          556

Sat 5/2/2026     538           840

Sun 5/3/2026    593           744

So I want to create a table that takes sales data from this year for 1 product and compare to data from another product in 2024 and align by day of the week.  The current year is fine.  I need to match 2024 with 2026 by day of the week.

[v-sshirivolu]

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

[Del235]

I did.  The one thing that I didn't include was the fact that Sales 1 and Sales 2 are in the same dataset.  They are a single measure Sales but Sales is filtered by event.  So there is a single date field as well.  That seems to make all of the solutions more difficult.

[v-sshirivolu]

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.

[v-sshirivolu]

Hi @Del235 ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us.

[v-sshirivolu]

Hi @Del235 ,

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

[cengizhanarslan]

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

[Del235]

What happens when both datasets are in the same file?  What do I use for dataset1, dataset2

[Ashish_Mathur]

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])

[mickey64]

For your reference.

Step 0: I use these data below.

<Data set 1>

<Data set 2>

Step 1: I make a calendar table below.

Calendar = CALENDARAUTO()

Weekday = WEEKDAY([Date],2)

Weeknum = WEEKNUM([Date])

Day of Week = SWITCH(TRUE(),[Weekday]=1,"Mon",[Weekday]=2,"Tues",[Weekday]=3,"Wed",[Weekday]=4,"Thurs",[Weekday]=5,"Fri",[Weekday]=6,"Sat",[Weekday]=7,"Sun")

Step 2: I add two relationships.

Step 3: I make a table.

[Shai_Karmani]

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