Calculating Year-over-Year Change using ISO Year and ISO Week in power BI

Question

My backend table has the data at a week level. It contain the current ISO year and current ISO week, as well as, the previous year's ISO year and week number that the current year's data should be compared with.

For each signup_iso_year-signup_iso_week combination, there exists only one iso_prev_year-iso_prev_yearweek combination. The iso_prev_year, iso_prev_yearweek columns account for the offset that might occur due to certain years having 53 weeks instead of 52.

data table

(I can't embed images, so I have added a table here as well, although it has much less information than the image in 'data table').

Number_of_signups	signup_iso_year	signup_iso_week	iso_prev_year	iso_prev_yearweek	Country	grade_level
5	2020	18	2019	18	IN	middle school
7	2020	18	2019	18	US	high school
6	2021	17	2018	18	IN	middle school
8	2021	17	2018	18	US	high school

I want to calculate to Year-Over_Year Change in number_of_signups using the signup_iso_year, signup_iso_week, iso_prev_year, iso_prev_yearweek columns.

I have already tried to create a calculated column that contains the sum of number_of_signups from previous year, but since every combination of country, grade_level, subject, email_type might not exist in previous or current year, some of the values are getting lost and hence giving incorrect results.

The answer I am looking for, is a Power BI measure that can give me the YOY change based on signup_iso_year and signup_iso_week.

Edit: I should have mentioned this before, but I forgot. The table contains data from 2018 to current day. So, the data size is quite large. Also, I need this YoY measure for a time series visual, which means that I can't assign ISOyear/ISOweek values for previous year using simple MAX/MIN functions. It needs to pick values from the iso_prev_year, iso_prev_yearweek columns but since EARLIER function can't be used in a measure, I am not able to figure out how to do that.

Which is why I had tried to create a calculated column, and use the EARLIER function to compute previous year's number_of_signups. But because of the other columns present in the data, i.e., country, grade_level, subject, email_type, there were discrepancies occurring in the actual number_of_signups and the calculated previous_year_number_of_signups. These discrepancies were due to the fact that not every combination of these columns exists for each week, so we might miss out on some data when calculating previous_year_number_of_signups.

Edit 2: Was asked to include examples of what the expected result would look like, so adding some pictures.

YoY at overall level, country level, grade level

YOY at country+grade level

mkRabbani mkRabbani · Accepted Answer · 2021-06-15T09:11:43

If I understand your requirement correct, you need a Measure like below. Remember, this may not the exact one you need, but this will definitely help you to reach your required output.

prev_signup = 

var iso_prev_year = MIN(your_table_name[iso_prev_year])
var iso_prev_year_week = MIN(your_table_name[iso_prev_yearweek])

RETURN
CALCULATE(
    SUM(your_table_name[Number_of_signups]),
    FILTER(
        ALL(your_table_name),
        your_table_name[signup_iso_year] = iso_prev_year
            && your_table_name[signup_iso_week] = iso_prev_year_week
    )
) + 0

You can also do some transformation in Power Query Editor and join the same table using your Key columns. That case, you can bring previous year's value in the same row. Rest is just compare 2 columns from your table to calculate YOY

Calculating Year-over-Year Change using ISO Year and ISO Week in power BI

1 Answers