Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have the following table. Let's say, the current week is number 9, i want to calculate the total submissions for week number 8 and week number 7, separately. I am not sure what is the best approach - should i consider by today's date or do some calculations to group the data by ISO week number (the first day of the week is Monday)?
Date | Student Name | Submissions |
13/02/2022 | Lizui | 4 |
14/02/2022 | Laufenburg | 7 |
16/02/2022 | Tegalpapak | 8 |
19/02/2022 | Ar Rabiyah | 5 |
20/02/2022 | Gangarampur | 3 |
22/01/2022 | Bellegarde | 3 |
15/02/2022 | Luntas | 2 |
18/02/2022 | Seedorf | 2 |
18/02/2022 | Frei Paulo | 6 |
20/02/2022 | Bellegarde | 3 |
23/02/2022 | Gangarampur | 3 |
25/02/2022 | Cosamaloapan de Carpio | 7 |
27/02/2022 | Luntas | 2 |
01/03/2022 | Zagrodno | 9 |
Here is an attempt but it does not work correctly since it adds an extra one to the total submissions for the previous week (number 8). I am not sure how to fix it.
Previous Week Total =
var _weekEnd = TODAY() - WEEKDAY(TODAY(),2)
var _weekStart = _weekEnd - 6
return
CALCULATE(SUM('Table'[Submissions]),ALL('Table'),'Table'[Date]>=_weekStart,'Table'[Date]<=_weekEnd)
And the total for the week number 7 which also gives an incorrect value:
Previous 2 Week Total =
var _weekEnd = TODAY() - WEEKDAY(TODAY(),2) - 7
var _weekStart = _weekEnd - 6 - 7
return
CALCULATE(SUM('Table'[Submissions]),ALL('Table'),'Table'[Date]>=_weekStart,'Table'[Date]<=_weekEnd)
Any help is much appreciated!
Solved! Go to Solution.
Hi @Anonymous ,
How about adding a "minus 1" to each of the dates. Like this:
TomsSubmissionMeasureLastWeek = VAR _lastWeek = WEEKNUM ( TODAY() -1, 1 ) - 1 RETURN CALCULATE ( SUM ( 'Table'[Submissions] ), WEEKNUM ( 'Table'[Date] -1 ) = _lastWeek )
I haven't tried it, but it might get you closer anyway 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi,
Please try the below.
The weeknumber starts from Monday.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure if I understood your question correctly, but the best way to solve this is to have Dim-Calendar Table with a week number column.
You can easily search how to create this.
If you cannot create a new table in your model, then please try to use WEEKNUM function like below.
Sorry that I quite do not undersand how to get the current week number = 9, but I hope you will easily understand the concept.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim Thanks for your reply!
Last week (the week should start with Monday) should be from 21 to 27 February and the correct total submissions should be 12.
And for the week before last week, it should only consider from 14 to 20 February, so the total should be 36.
I'm not sure how to change your code to get the desired results, as mentioned.
Hi,
Please try the below.
The weeknumber starts from Monday.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Anonymous ,
To solve your issue, I assumed that you were after the red submission (as last week) and the green marked submissions (as weekbeforelastweek):
Note, Power BI, by default, starts its weeks on Sundays. So today (Sunday, 6th of March) is a new week, meaning for last week (red) you get this:
Here the measure:
TomsSubmissionMeasureLastWeek = VAR _lastWeek = WEEKNUM ( TODAY(), 1 ) - 1 RETURN CALCULATE ( SUM ( 'Table8'[Submissions] ), WEEKNUM ( 'Table8'[Date] ) = _lastWeek )
For WeekBeforeLastWeek (green):
And here the respective measure:
TomsSubmissionMeasureWeekBeforeLastWeek = VAR _weekbeforelastWeek = WEEKNUM ( TODAY(), 1 ) - 2 RETURN CALCULATE ( SUM ( 'Table8'[Submissions] ), WEEKNUM ( 'Table8'[Date] ) = _weekbeforelastWeek )
Does this solve your issue?
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thank you for your reply! I would like to consider the total number of submissions by ISO week number, so the first day of the week should always be Monday.
So, for last week total submissions, it should be from 21 to 27 February. And for the week before last week, it should only consider from 14 to 20 February. Is it possible?
Hi @Anonymous ,
How about adding a "minus 1" to each of the dates. Like this:
TomsSubmissionMeasureLastWeek = VAR _lastWeek = WEEKNUM ( TODAY() -1, 1 ) - 1 RETURN CALCULATE ( SUM ( 'Table'[Submissions] ), WEEKNUM ( 'Table'[Date] -1 ) = _lastWeek )
I haven't tried it, but it might get you closer anyway 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
thanks Tom
very helpful
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |