Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tmhalila
Resolver II
Resolver II

Compute the difference between two value weekly

Hello,
I have a dataset that needs to present a trend visual for the net difference between two values weekly. The values are submitted by schools daily, I have created a measure to compute the latest number of students submitted by a school.

Now looking for your help to compute the difference in weeks.

Measure that computes weekly performance 

stud_number = 
VAR SelectedStartDate = MIN('dim_date'[date])
VAR SelectedEndDate = MAX('dim_date'[date])

RETURN
SUMX(
    VALUES(schools_subs[school_id]),
    VAR LatestDate =
        CALCULATE(
            MAX(schools_subs[submission_date]),
            schools_subs[school_id] = EARLIER(schools_subs[school_id]) &&
            schools_subs[submission_date] >= SelectedStartDate &&
            schools_subs[submission_date] <= SelectedEndDate
        )
    RETURN
        CALCULATE(
            SUM(schools_subs[students]),
            schools_subs[school_id] = EARLIER(schools_subs[school_id]) &&
            schools_subs[submission_date] = LatestDate
        )
)

 

Sample file: HERE

 

2 ACCEPTED SOLUTIONS
Sergii24
Super User
Super User

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:

Sergii24_0-1716819545422.png


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:

ALL( dim_date ) in variable _DimDateWithStudents and IF() statement in return (see comments for details). I'm having some issues adding the code here, please check measure "stud_number NEW Previous" in pbix attached.

Finally we can obtain a difference as 
[stud_number NEW] - [stud_number NEW Previous] (see measure "stud_number NEW delta with prev week" for details).

I'm attaching sample file where you can check everything in details.
Good luck with your project!

View solution in original post

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
            )
        )

View solution in original post

2 REPLIES 2
Sergii24
Super User
Super User

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:

Sergii24_0-1716819545422.png


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:

ALL( dim_date ) in variable _DimDateWithStudents and IF() statement in return (see comments for details). I'm having some issues adding the code here, please check measure "stud_number NEW Previous" in pbix attached.

Finally we can obtain a difference as 
[stud_number NEW] - [stud_number NEW Previous] (see measure "stud_number NEW delta with prev week" for details).

I'm attaching sample file where you can check everything in details.
Good luck with your project!

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
            )
        )

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.