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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate previous week total and previous 2 weeks total

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

 

DateStudent NameSubmissions
13/02/2022Lizui4
14/02/2022Laufenburg7
16/02/2022Tegalpapak8
19/02/2022Ar Rabiyah5
20/02/2022Gangarampur3
22/01/2022Bellegarde3
15/02/2022Luntas2
18/02/2022Seedorf2
18/02/2022Frei Paulo6
20/02/2022Bellegarde3
23/02/2022Gangarampur3
25/02/2022Cosamaloapan de Carpio7
27/02/2022Luntas2
01/03/2022Zagrodno9

 

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!

2 ACCEPTED SOLUTIONS

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! linkedIn

#proudtobeasuperuser 

View solution in original post

Hi,

Please try the below.

The weeknumber starts from Monday.

 

 

Previous week total: =
VAR maxdateintable =
TODAY()
VAR maxdateweeknumber =
WEEKNUM ( maxdateintable,2 )
VAR filterbyweek =
FILTER ( Data, WEEKNUM ( Data[Date],2 ) = maxdateweeknumber - 1 )
RETURN
SUMX ( filterbyweek, Data[Submissions] )
 
Previous 2 weeks total: =
VAR maxdateintable =
TODAY()
VAR maxdateweeknumber =
WEEKNUM ( maxdateintable,2 )
VAR filterbyweek =
FILTER ( Data, WEEKNUM ( Data[Date],2 ) = maxdateweeknumber - 2 )
RETURN
SUMX ( filterbyweek, Data[Submissions] )

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.


Go to My LinkedIn Page


View solution in original post

8 REPLIES 8
Jihwan_Kim
Super User
Super User

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.

 

 

Previous week total: =
VAR maxdateintable =
MAX ( Data[Date] ) // Or, better way is to use maxdateintable = today()
VAR maxdateweeknumber =
WEEKNUM ( maxdateintable )
VAR filterbyweek =
FILTER ( Data, WEEKNUM ( Data[Date] ) = maxdateweeknumber - 1 )
RETURN
SUMX ( filterbyweek, Data[Submissions] )
 
Previous 2 weeks total: =
VAR maxdateintable =
MAX ( Data[Date] )
VAR maxdateweeknumber =
WEEKNUM ( maxdateintable )
VAR filterbyweek =
FILTER ( Data, WEEKNUM ( Data[Date] ) = maxdateweeknumber - 2 )
RETURN
SUMX ( filterbyweek, Data[Submissions] )
 
 

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.


Go to My LinkedIn Page


Anonymous
Not applicable

@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.

 

 

Previous week total: =
VAR maxdateintable =
TODAY()
VAR maxdateweeknumber =
WEEKNUM ( maxdateintable,2 )
VAR filterbyweek =
FILTER ( Data, WEEKNUM ( Data[Date],2 ) = maxdateweeknumber - 1 )
RETURN
SUMX ( filterbyweek, Data[Submissions] )
 
Previous 2 weeks total: =
VAR maxdateintable =
TODAY()
VAR maxdateweeknumber =
WEEKNUM ( maxdateintable,2 )
VAR filterbyweek =
FILTER ( Data, WEEKNUM ( Data[Date],2 ) = maxdateweeknumber - 2 )
RETURN
SUMX ( filterbyweek, Data[Submissions] )

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.


Go to My LinkedIn Page


tackytechtom
Super User
Super User

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

tomfox_5-1646579061355.png

 

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:

tomfox_6-1646579126840.png

Here the measure:

TomsSubmissionMeasureLastWeek = 
VAR _lastWeek = WEEKNUM ( TODAY(), 1 ) - 1
RETURN
CALCULATE ( 
    SUM ( 'Table8'[Submissions] ),
     WEEKNUM ( 'Table8'[Date] ) = _lastWeek
)

 

For WeekBeforeLastWeek (green):

tomfox_7-1646579203912.png

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! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

Hi @tackytechtom 

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! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

thanks Tom

very helpful

Anonymous
Not applicable

@tackytechtom Thanks a lot! It works!

MakeItReal_0-1646581827305.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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