Forum Discussion
Measure that only uses 'previous week' values
- Anonymous9 years ago
Create a new column in either your Date Dimension table (if you are using one) or your Values table. Have this column be called "isLastWeek".
Make this column a formula that check whether that line is in the week prior to the current week, this would then store TRUE if it is and FALSE if it isn't.
On your reports, make a Report or Page filter use this column and only show values that are TRUE.
So how do we know what week is last week? Well we could use the WEEKNUM and YEAR functions to do this. Unless this week is the first week of the year, last week will be WEEKNUM minus 1 with the same year. So i'd do something like:isLastWeek = IF( WEEKNUM(TODAY(), 1) = 1, IF( AND( YEAR('Dim - Date Table'[Date]) = YEAR(TODAY()) - 1, WEEKNUM('Dim - Date Table'[Date]) = WEEKNUM(DATE(YEAR(TODAY()), MONTH(12), DAY(31))) ), TRUE(), FALSE() ), IF( AND( YEAR('Dim - Date Table'[Date]) = YEAR(TODAY()), WEEKNUM('Dim - Date Table'[Date], 1) = WEEKNUM(TODAY(), 1) ), TRUE(), FALSE() ) )
Note my code uses a table called 'Dim - Date Table'
Hi
I have tested the code and have adjusted for week commencing monday....
isLastWeek = IF(
WEEKNUM(TODAY(), 2) = 1,
IF(
AND(
YEAR('Ref Date'[Date]) = YEAR(TODAY())-1,
WEEKNUM('Ref Date'[Date]) = WEEKNUM(DATE(YEAR(TODAY()), MONTH(12), DAY(31)))
),
TRUE(),
FALSE()
),
IF(
AND(
YEAR('Ref Date'[Date]) = YEAR(TODAY()),
WEEKNUM('Ref Date'[Date], 2) = WEEKNUM(TODAY(), 2)-1
),
TRUE(),
FALSE()
)
)
Same code but for this week
isThisWeek = IF(
WEEKNUM(TODAY(), 2) = 1,
IF(
AND(
YEAR('Ref Date'[Date]) = YEAR(TODAY())-1,
WEEKNUM('Ref Date'[Date]) = WEEKNUM(DATE(YEAR(TODAY()), MONTH(12), DAY(31)))
),
TRUE(),
FALSE()
),
IF(
AND(
YEAR('Ref Date'[Date]) = YEAR(TODAY()),
WEEKNUM('Ref Date'[Date], 2) = WEEKNUM(TODAY(), 2)
),
TRUE(),
FALSE()
)
)