How to do the YOY/MOM/QOQ if the sales data are in two different files and format?

[MC_2023]

Hi,

I have historical sales data from an old system.

And now have updated sales data from another new ERP system. So the format/ field names may be different. 

Then how can I perform the sales comparison (YOY/QOQ/MOM)? 

Appreciate any experts can help on this issue. 

[rohit1991]

Hi @MC_2023 ,
To compare Year-over-Year (YOY), Quarter-over-Quarter (QOQ), and Month-over-Month (MOM) sales when data comes from two different files and formats, start by consolidating the datasets in a common platform like Excel, Python, SQL, or Power BI. Standardize column names, date formats, and sales figures to ensure consistency.

If field names differ, map them accordingly, and align product or category identifiers between the old and new systems. Once standardized, merge historical and new sales data while ensuring no duplicates. After integration, use pivot tables, SQL queries, or Python libraries like Pandas to calculate YOY, QOQ, and MOM changes by comparing sales figures across corresponding time periods.

[sanalytics]

@MC_2023 
If you having two different datasets with same grain then the 1st option should be try to append both datasets into one. if the date format is different then you need to convert those date format to 1st table date format. Use power query editor for that.
If you are unable to do that then you can follow below approach as well.
1) Create a combined total sales measure which can sum the sales of bothe table. for example
 Total Sales = 
 VAR _table1 = sum(table1[Sales])
 VAR _table2 = sum(table2[Sales])
Return
_table1+table2
2) then create Date dimension. the Date dimension should have two column with two different formatted. For example, the first table date format is dd/mm/yy then make the date column of date dimension of the same format and create the another date column of date dimension which is format of second table.
3) Create an Active relationship of date dimension and first table and inactive relationship between date dimension and second table.
4) Since only one relationship can be active, use userrelationship function for that.

something like below 
Total Sales (With Date) =
VAR Sales1 = sum(table1[sales])
VAR Sales2 = calculate(sum(table2[Sales]), USERELATIONSHIP(table2[Date2], DateTable[Date]))
RETURN Sales1 + Sales2
5) Now you calculate the yoy,qoq and mom measure..
if you face any challenges then requesting you share some dummay data along with your schema structure and desired output so that we can try from our end and help you.

Hope it will help you.

Regards

sanalytics

[ChielFaber]

This sounds like the perfect use case to solve with Power Query. You can clean and transform data from both systems, standardize column names, map fields, and easily adjust data types — all within a user-friendly interface. Power Query handles data from different sources seamlessly and offers powerful tools to filter, split, merge, and shape your data to match your desired format.