Forum Discussion

Del235's avatar
Del235
Helper III
3 months ago
Solved

Aligning Data With different dates

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/...
Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    3 months ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 months ago

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