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
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
Solved! Go to Solution.
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 =
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
)
)
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 =
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
)
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
103 | |
93 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
101 | |
95 |