want to calculate year over year change on weekly basis ie for last week

[priyam0003]

I need to calculate year-over-year percentage change on a weekly basis i.e. for last week.

I have a total sales custom measure which calculates the total sales of the organisation.

Please help me with how to do this. 

[Martin_D]

Hi @priyam0003 

Do you 

want to compare one week with one week ago or compare one week with the week one year ago?

BR

[priyam0003]

I want to compare one week with the week one year ago.

[Martin_D · Accepted solution]

With week based calculations there are a lot of details that you need to specify explicitly in order to get what you need, including what day is start of week, what should happen around new year, and how should weeks without data affect the user experience and what you see, if this scenario is relevant for your use case.

Here are two solutions for common scenarios. In all examples, 'Financials' is the fact table and 'Financials'[Sales] is the column containing the values.

1. Select one week and compare with the seven days that are one year ago. In this case, the selected week might be one calendar week, e.g. Monday to Sunday, but the same dates minus one year will not be one calendar week but e.g. Wednesday to Tuesday of the following week in the previous year.

Steps inclused:

1. Create a measure to calcualte the value of the selected period. The measure is not specific to week based calculations. The week selection comes from a filter or slicer.
   DAX code for a measure:
   
   Total Current Period = SUM ( 'Financials'[Sales] )
2. Create a measure that calculates the same measure for one year ago. This example does not use a date table. If your data model uses a date table, replace the date column in the DAX code with the date column of your date table.
   DAX code for a measure:
   
   Total Previous Year =

   CALCULATE (

       [Total Current Period],

       SAMEPERIODLASTYEAR ( 'Financials'[Date] )

   )
3. Create a measure for the year-over-year calculation and format the measure as %.
   DAX code for a measure:
   
   YoY % = DIVIDE ( [Total Current Period] - [Total Previous Year], [Total Previous Year] )
4. Select a week in a filter, e.g. add the date column to filters for all pages and select the relative date filter for the last week:
   
5. Use the measures in visuals of your choice.

2. Second approach compares the selected calendar week with the same calendar week number in the previous year. If the selected week is calendar week 53 and the previous year has only 52 weeks, then week 53 is compared against week 52 of the previous year (you can specify and implement different behavior as well, of course).

Steps included:

1. As before, create a measure to calcualte the value of the selected period. The measure is not specific to week based calculations. The week selection comes from a filter or slicer.
   DAX code for a measure:
   
   Total Current Period = SUM ( 'Financials'[Sales] )
2. Create a date table that contains the week numbers and years.
   DAX code for a calcualated table:
   
   Date =

   ADDCOLUMNS (

       ADDCOLUMNS (

           CALENDARAUTO ( ),

           "Week",

           WEEKNUM ( [Date], 21 )

       ),

       "Year of Week",

       YEAR ( [Date] )

       + IF (

           MONTH ( [Date] ) = 1 && [Week] >= 52,

           -1,

           IF (

               MONTH ( [Date] ) = 12 && [Week] = 1,

               1,

               0

           )

       ),

       "Weekday",

       WEEKDAY ( [Date], 3 ),

       "Date formatted as Weekday", // formatting to be done in Power BI Desktop formatting tools

       [Date]

   )
3. Create a one to many relationship from the date column of the date table to the date column of the fact table. From now on, you should not use the date column from your fact table anymore in visuals or filters, instead use fields from the date table! You can hide the date column from the fact table to prevent unintentional use.
   
4. Create a measure to calculate the value for the previous year's week. This measure only produces useful results if used in the context of one calendar week are a subset of days of a calendar week!
   DAX code for a measure:
   
   Total Same Week Previous Year =

   VAR _SelectedYear = MAX ( 'Date'[Year of Week] )

   VAR _SelectedWeek = WEEKNUM ( MAX ( 'Date'[Date] ), 21 )

   VAR _PreviousYear = _SelectedYear - 1

   VAR _PreviousYearWeek =

       IF (

           _SelectedWeek = 53,

           MAXX (

               FILTER (

                   ALL ( 'Date' ),

                   [Year of Week] = _PreviousYear

               ),

               [Week]

           ),

           _SelectedWeek

       )

   VAR _Weekdays = VALUES ( 'Date'[Weekday] )

   RETURN

   CALCULATE (

       [Total Current Period],

       REMOVEFILTERS ( Financials[Date] ), // not needed if you commit to not using the date column of the fact table in any filter or visual or context changing DAX query

       REMOVEFILTERS ( 'Date' ),

       'Date'[Week] = _PreviousYearWeek,

       'Date'[Year of Week] = _PreviousYear,

       TREATAS ( _Weekdays, 'Date'[Weekday] )

   )
5. Create a measure for the year-over-year calculation and format the measure as %.
   DAX code for a measure:
   
   YoY % week based = DIVIDE ( [Total Current Period] - [Total Same Week Previous Year], [Total Same Week Previous Year] )
6. As before, select a week in a filter, e.g. add the date column to filters for all pages and select the relative date filter for the last week, or use the weeks from the new date table as categories in a visual.
7. Use the measures in visuals of your choice.

A sample solution can look like this. Be aware that in my sample data there isn't data for every day:

BR
Martin