How to calculate sum of this monday to sunday value - Measure

[Anonymous]

Hi

 How to calculate sum of this week's monday to sunday values & the following week's sum of monday to saunday values.

  ex

this week monday 23/03/2020 to sunday 29/03/2020

following week 30/03/2020 to sunday 05/03/2020  

Thanks in anticipation

Kavitha ghosh

[Anonymous · Accepted solution]

@Anonymous 

There is a problem with the week number method. For the last week of this year is 2020,12,28 - 2021,1,3, the week numbers are different(53 and 1) although they are in the same week. To prevent this problem, I would recommend you to use monday as the flag.

In this way, this week would be dates between thismonday and this sunday(monday+6), and next week would be next monday, next sunday(next monday+6).

Measure = 
var thismonday=MAXX(FILTER('Table',[Date]<=TODAY()&&WEEKDAY('Table'[Date],2)=1),'Table'[Date])
var nextmonday=MINX(FILTER('Table',[Date]>=TODAY()&&WEEKDAY('Table'[Date],2)=1),'Table'[Date])
Return CALCULATE(SUM('Table'[Value]),DATESBETWEEN('Table'[Date],thismonday,thismonday+6))

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RMB]

The Weekday and Weeknum functions should be capable of doing this for you.

A quick example below, you would want to add a year check.

WeekTotals =
VAR __ThisWeek = WEEKNUM(TODAY(), 2)
VAR __NextWeek = __ThisWeek + 1

RETURN
CALCULATE(SUM('Table1'[ColumnToSum]), FILTER('Table1', WEEKNUM(T1[Date],2) = __ThisWeek))

[amitchandak]

@Anonymous , You can create a date calendar and week start and week end based on that. You can create a week name based on that. Refer, how to so in the calendar and how to use it

https://www.dropbox.com/s/a9xq913pgvuzg2x/sales_analytics_weekWiseMon_sun.pbix?dl=0