Track your weekly marketing campaign metrics using Dax in Excel — Part 4 — Week-on-Week Analysis

Having explored dynamic KPI selection in our previous installment, we now journey deeper into the analytics realm. This segment is about gaining comparative insights. Specifically, we’ll enable users to contrast a week’s metrics with the preceding week, providing a richer perspective on marketing campaign performance. This can be instrumental in spotting trends, understanding fluctuations, and drawing actionable conclusions.

Adding Comparative KPIs to the Mix

Our goal here is twofold: first, to identify if a single week has been selected by the user, and second, to fetch the metrics for the week just prior to the selected one. This sets the stage for our week-to-week comparison.

SingleWeekSelected Measure:

SingleWeekSelected:=HASONEVALUE(Tb_Calendar[Week Name])

This simple yet essential measure checks if only one week is chosen from our slicer. It ensures that our subsequent calculations, meant for week-to-week comparisons, make logical sense to the end-user.

SelectedKpiPrevWeek Measure:

SelectedKpiPrevWeek:=
VAR wk = MIN(Tb_Calendar[WeekNumAcc]) -1
VAR KpiPrevWweekOnly = CALCULATE( [SelectedKPI] , 
                                            FILTER( ALL( Tb_Calendar[Week Name], Tb_Calendar[WeekNumAcc] ) , Tb_Calendar[WeekNumAcc] = wk)
                                  ) 
 VAR result = if ( [SingleWeekSelected], KpiPrevWweekOnly, BLANK() ) 
RETURN result

Building on our previously established measures, this formula retrieves the value of the chosen KPI for the week preceding the selected one. With the use of variables (VAR), it first identifies the previous week, then calculates the KPI for that week, and finally ensures this is only shown when a single week is selected.

PercentChangeFromPrevWeekForSelectedKpi Measure:

PercentChangeFromPrevWeekForSelectedKpi:=IFERROR(  ([SelectedKPI] - [SelectedKpiPrevWeek]) / [SelectedKpiPrevWeek] ,BLANK())

With the metrics for both the selected week and its predecessor in hand, we craft this measure to display the percentage change between the two. It vividly showcases the upswing or downturn in a given KPI, providing users with an immediate sense of performance dynamics.

To ensure clarity: Ensure that the PercentChangeFromPrevWeekForSelectedKpi measure is formatted as a percentage in the data model.

A Glimpse into Our Progress

To give you a clearer understanding of the changes we’ve incorporated, I’ve attached a screenshot. In this visual representation, you’ll see the SelectedKPI, SelectedKpiPrevWeek, and their percentage difference. For the sake of demonstration, the slicer has been set to the week 2023-09.

Concluding Thoughts

Incorporating week-to-week comparative analysis adds a layer of depth to our dashboard. Users can now not only view metrics for a particular week but also instantly gauge how it stacks up against the prior week. This can be instrumental in adjusting marketing strategies, allocating budgets, or simply understanding the momentum of specific campaigns.

As we wrap up this part, the transformative power of DAX in rendering Excel a potent business intelligence tool becomes even more evident. Stay with us as we venture further into the nuances of DAX and unravel more sophisticated analytical capabilities in our upcoming segments.

For those eager to follow along, you can download the dataset from this link. Having the data on hand will provide a practical experience as we navigate through each step together.