Forum Discussion
Solved
Sum over current week
Hi everybody, I want to create a simple measure but just can't figure out how to write it properly. Here we go: Let#s say I have a table with two columns: Date | Logged in 1.1.2018...
To replicate your problem I have created a table with a date field and a loggedin field that is populated with numbers.
The following measures will give you the sum of the logged in values only for the week of the date set in the _Today Variable. If you want this to be the current date then set the variable to be equal to UTCTODAY()
Current Week = VAR _Today = DATE ( 2018, 1, 8 ) VAR _StartOfWeek = _Today - WEEKDAY ( _Today, 2 ) + 1 VAR _EndOfWeek = _Today + ( 7 - WEEKDAY ( _Today, 2 ) ) RETURN IF ( MAX ( Table1[date] ) >= _StartOfWeek && MAX ( Table1[date] ) <= _EndOfWeek, SUM ( Table1[loggedin] ), BLANK () )
To replicate your problem I have created a table with a date field and a loggedin field that is populated with numbers.
The following measures will give you the sum of the logged in values only for the week of the date set in the _Today Variable. If you want this to be the current date then set the variable to be equal to UTCTODAY()
Current Week =
VAR _Today =
DATE ( 2018, 1, 8 )
VAR _StartOfWeek =
_Today - WEEKDAY ( _Today, 2 ) + 1
VAR _EndOfWeek =
_Today
+ ( 7 - WEEKDAY ( _Today, 2 ) )
RETURN
IF (
MAX ( Table1[date] ) >= _StartOfWeek
&& MAX ( Table1[date] ) <= _EndOfWeek,
SUM ( Table1[loggedin] ),
BLANK ()
)Anonymous great, thank you! I learned something new :)
