Compute the difference between two value weekly

Hi tmhalila, first of all thank you for a great question and pbix sample! It makes it much easier to help you!

Comparison of time periods is not so simple as it might look like. The easiest and most straight forward way to acheive the desired result is to work with concept of "relative date" (relative week in your case). Here is the final result:

Now some details 🙂 Let's start by adding two new column in "dim_date" table, we'll use them in further calcualtions.

• YearWeek Number  - a number, that we will use it to define a relative position of any selected week
• Relative Week - relative number of week in your dataset (required to compare with previous week)

YearWeek Number = 
VAR _CurrentYear = "20"&RIGHT( dim_date[weekly_start_monday_period], 2 )
VAR _CurrentWeek = MID( dim_date[weekly_start_monday_period], 2, 2 )
RETURN INT( _CurrentYear & _CurrentWeek )                                //INT transforms concatenation, which is a text in number

Relative Week = 
VAR _CurrentYearWeek = dim_date[YearWeek Number]            //get the current YearWeek Number
VAR _RankDimDateUntilCurrentWeek =                      
    FILTER(
        VALUES( dim_date[YearWeek Number] ),                //obtain list of unique YearWeek Number in dim_date (for the whole dim_date, it's not filtered) 
        dim_date[YearWeek Number] < _CurrentYearWeek        //now filter it with values less than current YearWeek Number
    )
    
RETURN
    COALESCE(
        COUNTROWS( _RankDimDateUntilCurrentWeek ),          //countrows of temporary table to get the relative number of week comparing to the global monimum  
        0                                                   //0 is needed for global minimum week, as there is no week less than that one, so relative position should be 0
    )

Once done, we can come back to the real measures. The first question to answer is: what should I do if I combine more weeks together? Do I want to show anything? Should it be the max of selected weeks or the last week for which we have number of students? Let's say we go with the first hypothesis, so when weeks are aggregated, we'll show the value for max relative week:

stud_number NEW = 
VAR _CurrentMAX_RelativeWeek = MAX( dim_date[Relative Week] )           //MAX relative week in current filter context
VAR _DimDateWithStudents = 
    ADDCOLUMNS(
        FILTER(                                                         //obtain dim_date table filtered with only selected week, so in most cases we'll obtain a table of 7 rows (number of days per week)
            dim_date,
            dim_date[Relative Week] = _CurrentMAX_RelativeWeek
        ),
        "@Students", CALCULATE( SUM( schools_subs[students] ) )         //add number of students for each date. Calculate is used to pass currently interating date as a filter to schools_subs table
    )
VAR _ValueOfMaxNotEmptyDate =                                           //now let's filter the table from above to the last date for which we have some number of students. It will help us to deal with cases if for one week a school has submitted records on Friday and the week after on Sunday  
    TOPN(
        1,
        FILTER(
            _DimDateWithStudents,
            [@Students] > 0
        ),
        [date],
        DESC
    )

RETURN                                                                  //using SUMX to obtain a single value because measure can't return a table
    SUMX(
        _ValueOfMaxNotEmptyDate,
        [@Students]
    )

Now we create a new measure to obtain the same value for previous week. The peculiarities of a measure are:

Wow! Thank you very much, for the response! I was also trying to do it and got the easiest way just to deal with period while maintaining the first measure that compute the number of students.

HERE is my solution:

Weekly_Difference = 
VAR CurrentWeekStartDate =
    MIN ( 'dim_date'[date] ) - WEEKDAY ( MIN ( 'dim_date'[date] ), 2 ) + 1
VAR PreviousWeekStartDate = CurrentWeekStartDate - 7
RETURN
    CALCULATE (
        [stud_number],
        FILTER (
            ALL ( 'dim_date' ),
            'dim_date'[date] >= CurrentWeekStartDate
                && 'dim_date'[date] < CurrentWeekStartDate + 7
        )
    )
        - CALCULATE (
            [stud_number],
            FILTER (
                ALL ( 'dim_date' ),
                'dim_date'[date] >= PreviousWeekStartDate
                    && 'dim_date'[date] < CurrentWeekStartDate
            )
        )