We have a sales report about products P1 to P10 by quarters from Q1/23 to Q4/24.

Which of the following products saw the largest increase in sales from 2023 to 2024 ?

We can calculate manually the sums for 2023 and 2024 and calculate the change between the years. Sales for P5 have increased mostly when measure the relative change.  

This way we need manual adjustments. We could make a data model and count with DAX.

The data is not in pivot format. The data should include fields product, quarter and sales volume. The data should not be in matrix format.

Download the data into Power Query, activate the first column and then select right mouse button and unpivot other columns.

Now the data is in Pivot format. Add the column headers.

The power query data is loaded into data model. Each row contains product, quarter and sales volume.

Choose close & load to…

Select only create connection and activate add this data to the data model.

Now we have the same data but on top of the data model.

We need the formulas to calculate the year total with DAX.

SUM23 = CALCULATE(SUM(Pivot2[Sales]);Pivot2[Quarter] IN {“Q1/23″;”Q2/23″;”Q3/23″;”Q4/23”})

SUM23 sums the quarters from 2023.

Pivot2 is the table. Sales is the field for sales volumes. Quarter is the time field. Q1/23, Q2/23 and so on are the values in quarter field.

The same pattern works for the total sales in 2024:

SUM24 = CALCULATE(SUM(Pivot2[Sales]);Pivot2[Quarter] IN {“Q1/24″;”Q2/24″;”Q3/24″;”Q4/24”})

Finally, we calculated the change between the years.

Change = ([SUM24]-[SUM23])/[SUM23]

Sales for P5 increased most between 2023 and 2024 compared to other products.

We have the same results but with less manual work. We have the data in data model and DAX was calculating the results. The benefit with data model is, that we can create other measures with DAX on top of the data model.